Function based on criteria

[Craig Mowbray]

Hello Again,
I've been using the lookup and countif functions alot, my favorite
functions!
Anyways i would like to advance these fuctions a little.

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, Column "B" is a invoice date,
"C" is a type, "D" is a type, 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 "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 Once Again!
Craig

 

[Dan E]

Craig,

The SUMPRODUCT often works for these types of applications

=SUMPRODUCT((MONTH(B1:B6)=MONTH($A$1))*(YEAR(B1:B6)=YEAR($A$1))*(C1:C6=1)*(D16="A")*E1:E6)
returns 300 (i assumed you wanted a sum not a count based on
your desired result, a count would return 2 (2 rows match your
criteria, if it was a count you wanted...
=SUMPRODUCT((MONTH(B1:B6)=MONTH($A$1))*(YEAR(B1:B6)=YEAR($A$1))*(C1:C6=1)*(D16="A"))

Sample logic (each statement returns True of False (1 or 0))
(MONTH(B1:B6)=MONTH($A$1)) returns (0 0 1 1 1 1)
(YEAR(B1:B6)=YEAR($A$1)) returns (1 1 1 1 1 1)
(C1:C6=1) returns (1 1 1 0 1 0)
(D16="A") returns (1 1 1 0 1 0)
E1:E6 = (200 300 100 200 200 200)

For the count and sum multiply each column together
0 * 1 * 1 * 1 = 0 0 * 1 * 1 * 1 * 200 = 0
0 * 1 * 1 * 1 = 0 0 * 1 * 1 * 1 * 300 = 0
1 * 1 * 1 * 1 = 1 1 * 1 * 1 * 1 * 100 = 100
1 * 1 * 0 * 0 = 0 1 * 1 * 0 * 0 * 200 = 0
1 * 1 * 1 * 1 = 1 1 * 1 * 1 * 1 * 200 = 200
1 * 1 * 0 * 0 = 0 1 * 1 * 0 * 0 * 200 = 0
add the results
(0 + 0 + 1 + 0 + 1 + 0) = 2
(0 + 0 + 100 + 0 + 200 + 0) = 300

Dan E