Countif function based on multiple criteria

[Craig Mowbray]

Hello,
I would like to use countif or another worksheet function to add cells that
fit multiple criterias.

A B C D E F
1 09/01/03 08/13/03 1 A 200 300
2 08/17/03 1 A 300 100
3 09/05/03 1 A 100 300
4 09/08/03 2 B 200 500
5 09/14/03 1 A 200 300
6 09/20/03 2 B 200 500

A1 will alway be a first of the month date (in this case Sept 1, 2003) and
criteria #1, Column "B" is a invoice date,
"C" is a criteria #2, "D" is a criteria #3, E-F are the data. I wish to use
A1 as a search for column "B" to match all dates that are in the A1's Month
and Year, in
this case I'm looking for all dates in column "B" that are September-2003.
Then
from thoses I wish to define my search to column "C", lookin for "1", then I
wish define my search once more to column "D", looking for "A", then I wish
to add up column "E" with countif function. In this case the total would be
300.
Thanks in Advance!
Craig

 

[JMay]

Try:
=SUMPRODUCT(($B$1:$B$6>=$A$1)*($B$1:$B$6<=EOMONTH($A$1,0))*($C$1:$C$6=$J$2)*
($D$1:$D$6=$J$3)*($E$1:$E$6))

In Cell J2 enter the desired Column C Code and in J3 enter the desired
Column D Code.

HTH

 

[Bob Phillips]

Hi Craig,

Here's a solution

=SUMPRODUCT((MONTH(B1:B6)=MONTH(A1))*(YEAR(B1:B6)=YEAR(A1))*(C1:C6=1)*(D16
="A"),E1:E6)

watch wrap-around, it's all on one line.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Craig Mowbray]

Thanks Bob, worked great with my ranges, just wondering also, I reference my
Record Worksheet and since data is alway being added to it, is there a way
to reference the columns to search without using (Record!A3:A65536) as I
have used in my function.
I only wish to search the rows in these column up to the current end row.
Also using the references like this would they slow the processing down??
I will have to practice using =SumProduct, look like a very powerful
function!

=SUMPRODUCT((MONTH(Record!A3:A65536)=MONTH(J2))*(YEAR(Record!A3:A65536)=YEAR
(J2))*(Record!D365536="TD")*(Record!Q3:Q65536=Data!A30),Record!E3:E65536)

 

[Bob Phillips]

Craig,

You can but it is a bit messy. You can dynamically determine the end by
counting the cells in a column that are occupied. In this case, column B is
the best column. The number here is
COUNT(B:B)

Using this you reference the last cell, like so
OFFSET(B1:COUNT(B:B)-1,0)
which is the number of cells past B1, -1 to get the last.

This in turn can be used in a range like
B1:OFFSET(B1:COUNT(B:B)-1,0)
which then references the range B1 to the last occupied cell in column B.

This then makes the formula

=SUMPRODUCT((MONTH(B1:OFFSET(B1,COUNTA(B:B)-1,0))=MONTH(A1))*(YEAR(B1:OFFSET
(B1,COUNTA(B:B)-1,0))=YEAR(A1))*(C1:OFFSET(C1,COUNTA(B:B)-1,0)=1)*(D1:OFFSET
(D1,COUNTA(B:B)-1,0)="A"),(E1:OFFSET(E1,COUNTA(B:B)-1,0)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)