If statements & countif(s)

[cocoonfx]

Hello


I have a spreadsheet where i have to count the number of claims which have been made.

For example i have cell b2:b6000 inwhich i have a list of valables which would be 601 or 602 i then i have cell g2:g6000 which has claim and no claim.

I want to find out the total number of claims for 601?

i have tried a combination of if statements and countif statements which doesn't work does any one know how i can do this?

 

[reclusivemonkey]

You need to add another column. In it, put

=IF(B2="601",G2,"")

Then at the bottom of this column (lets say its H)

=COUNTIF(H2:H6000,"Claim")

 

[qafself]

The other way to do this is using SUMPRODUCT which is a bit cleaner

=SUMPRODUCT(--(B2:B6000=601)*(G2:G6000="Claim"))

Ed

 

[reclusivemonkey]

The other way to do this is using SUMPRODUCT which is a bit cleaner

=SUMPRODUCT(--(B2:B6000=601)*(G2:G6000="Claim"))

Interesting, I didn't know you could filter like this in formulas.

 

[qafself]

I'm just starting to get into SUMPRODUCT - it can be very useful and gets rid of the need for intermediate results or helper columns

 

[HaHoBe]

Hi, qafself,

have you already come across Sumproduct?

Ciao,
Holger

 

[qafself]

Holger,

No, I hadn't until you gave me the link - thanks, I'm sure it will teach me some more! That's the beauty of these forums, there is always someone to help you and who can point you in the right direction for further information and learning.

Ed