Countif function based on multiple criteria

  • Thread starter Thread starter Craig Mowbray
  • Start date Start date
C

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
 
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
 
Hi Craig,

Here's a solution

=SUMPRODUCT((MONTH(B1:B6)=MONTH(A1))*(YEAR(B1:B6)=YEAR(A1))*(C1:C6=1)*(D1:D6
="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)
 
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!D3:D65536="TD")*(Record!Q3:Q65536=Data!A30),Record!E3:E65536)
 
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)
 
Back
Top