Microsoft Excel (1 Viewer)

H

Harry Taylor

Registered User.

Local time

Today, 03:29

Joined

Jul 10, 2012

Messages

70

• Jun 5, 2013

• #1

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?

 

D

DavidAtWork

Registered User.

Local time

Today, 03:29

Joined

Oct 25, 2011

Messages

699

• Jun 5, 2013

• #2

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

 

D

DavidAtWork

Registered User.

Local time

Today, 03:29

Joined

Oct 25, 2011

Messages

699

• Jun 5, 2013

• #3

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

 

H

Harry Taylor

Registered User.

Local time

Today, 03:29

Joined

Jul 10, 2012

Messages

70

• Jun 5, 2013

• #4

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 ???

 

S

syswizard

Registered User.

Local time

Yesterday, 22:29

Joined

Dec 27, 2008

Messages

61

• Jun 5, 2013

• #5

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

 

H

Harry Taylor

Registered User.

Local time

Today, 03:29

Joined

Jul 10, 2012

Messages

70

• Jun 5, 2013

• #6

syswizard,

You are a star
Worked perfectly, thanks

 

D

DavidAtWork

Registered User.

Local time

Today, 03:29

Joined

Oct 25, 2011

Messages

699

• Jun 5, 2013

• #7

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