Microsoft Excel

[Harry Taylor]

Gents,

can anyone help with an Excel formula?

I've been asked to count how many times an occurrence happens over 3 columns.

The formula I have so far looks like this (which doesn’t work)
=if(V;V="DAA",if(H:H="DAA",countif(E:E="CB")))

Basically count how many rows where column V=”DAA”, column H=”DAA” and column E=”CB”.
I know this would be fairly easy in access, but it must be in excel.
Any help would be greatly appreciated?

 

[DavidAtWork]

If you mean you want to count only the rows where all 3 values occur in the same row, then the way I would do it would be to use an additional column (which can be hidden if necessary) which would be a concatenation of columns V,H,E and then use Countif on this column where the value is DAADAACB

David

 

[DavidAtWork]

If you mean you want to count only the rows where all 3 values occur in the same row, then the way I would do it would be to use an additional column (which can be hidden if necessary) which would be a concatenation of columns V,H,E and then use Countif on this column where the value is DAADAACB

David

 

[Harry Taylor]

Thanks David,

I was trying to avoid an additional column, but it may ben my only option.

I have also tried the SUMPRODUCT;
=SUMPRODUCT(V2:V1010="DAA",H2:H1010="DAA",E2:E1010="CB")

but this returns a value of 0 in every field ???

 

[syswizard]

=countifs(e2:e3000,"=cb",h2:h3000,"=vaa",v2:v3000,"=vaa")

 

[Harry Taylor]

syswizard,

You are a star
Worked perfectly, thanks

 

[DavidAtWork]

ah yes syswizard's method will work if you have Excel 2007 ->