Excel Formula question "Countif"

G

George1

Guest
Hi,
Does anyone know if you can use two "Countif" functions in one formula?
I am trying to pull specific information from a large table to put into a graph.

Eventually, I did get the information I needed by using a Pivot table but it's not ideal.

Any help\ideas would be appreciated.

Regards
G1
 
Did you get this sorted? I think (don't have my work PC with me and cant log in from work:() that I did this once - countif yes and countif no in the same formula. Try to dig it out tomorrow.
 
you can't (or i dont think you can !) use two countif functions within the one formulae for the purpose you are looking for, however, if you combine the two logical statements together and then use the one countif statement that might work.
it means using an extra column but will get you the desired result without having to go done the pivot table route

A B === create a column with AB
A C === create a column with AC
A A === create a column with AA

then do a countif on the new column on the criteria countif = AB etc

does this help ?
 
Hey George1,
If you just add the 2 countifs does that give you what you need?

=(COUNTIF(A1:A24,"term1")+COUNTIF(A1:A24,"term2"))
 
Hi All,
Thanks for your help.
I tried the suggestion of adding the two "Countifs". It didn't give me exactly what I was looking for.
The "Array function", I can't fully understand it sorry, but tried the examples on Excel.

To try and explain what I am trying to achieve:

NameLocationRatingCompany 1Galway1Company 2Cork1Company 2Dublin2Company 1Athlone4Company 1Carlow5Company 2Kilkenny3Company 1Laois2Company 1Kerry3

Rating from 1 to 51Poor2Below Average3Average4Good5Excellent

There are two companies around Ireland whom I carried out a survey with to rate a service (from 1 to 5) being provided to them.
If it is possible what I want to do is,
See how many 1's, 2's 3's etc... Company1 and Company2 received and I want to put this in to the format below. I want to have these formula driven as the table values will change from time to time.

RatingCompany1Company2PoorBelow AverageAverageGoodExcellent

Thanks again.
George1
 
Don't know what layout your spreadsheet is, but guessing that the criteria is columns A and B, you could try this formula:

=sumproduct(--(A1:A100="criteria1"),--(B1:B100="Criteria2))

The range will obviously need amending
 
Thanks Lemister,
It got it sorted after much [FONT='Calibri','sans-serif']Interrogation of Excel help and advice from other forum member.[/FONT]
[FONT='Calibri','sans-serif']This was the formula that worked for me: =sumproduct((A2:A11=H13)*(E2:E11=G14)). I was also advised that if you use Excel 2007 there is another formula besides "Sumproduct" that works, "COUNTIFS".[/FONT]

[FONT='Calibri','sans-serif']Thanks again for your reply.[/FONT]
[FONT='Calibri','sans-serif']Regards[/FONT]
[FONT='Calibri','sans-serif']G1[/FONT]
 
You must log in or register to reply here.
Back
Top