Solved: COUNT IF Multiple Criteria

[Hoopsah]

Hi

I have a spreadsheet where column C holds a date (dd/mm/yy)

Column G holds a region name (i.e. Ness)

Column L holds an advisors name

Basically I am trying to get a formula that will count all the entries in Column L for a particular REGION and a particular MONTH

so,

If C1:C5000 = May & G1:G5000 = Ness COUNT L

Any help would be appreciated

[Bob Phillips]

Try

=SUMPRODUCT(--(MONTH(C1:C5000)=5),--(G1:G5000="Ness"))

[Hoopsah]

Hi Bob,

sorry, should have stated that all my data was on a sheet called Process 3, however, this was the code that I had originally used:

=SUMPRODUCT(--(MONTH('Process 3'!$C$1:$C$5000)=4),--(YEAR('Process 3'!$C$1:$C$5000)=2008),--('Process 3'!$G$1:$G$5000=B5),'Process 3'!$L$1:$L$5000)

but it keeps coming back with #VALUE!

I was assuming that it wasn't counting because Column L wasn't a number but a name.

[Bob Phillips]

Count L is irrelevant, You want a count of where C and G meet your criteria. The only test you might do on L is that it is not blank.

... unless you wan t to count the unique items in L that match the criteria in C and G...

[Hoopsah]

Hi Bob,

I think I might be attacking this the wrong way.

Columns C & G can contain data but then I only want to count the cells in L that have data in C & G and count column N that will have data in C & G.

I have started a sheet 1 trying to just import the data that I require from the master spreadsheet, in order to fill the cells in the tab Waste.

I'll attach a copy so you can see what I am doing

cheers

[Bob Phillips]

Gerry, you just need the formula as suggested

=SUMPRODUCT(--(MONTH( 'Process 3'!$C$2:$C$5000)=4),--(YEAR('Process 3'!$C$2:$C$5000)=2008),--('Process 3'!$G$2:$G$5000=C2),--('Process 3'!$L$2:$L$5000<>""))

problem is that your data is bad. For instance, C837 is 26.09.2008, that is not a date

[Hoopsah]

:banghead: :banghead:

Never even noticed that Bob (obviuosly)

Thanks for checking that and correcting me - formula works perfectly now.

Thanks again Bob

Gerry