Function based on criteria

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

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
 
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)*(D1:D6="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)*(D1:D6="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)
(D1:D6="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
 
Back
Top