Sum data if 3 criteria are met

[rdbjr99]

I'm trying to come up with a formula that will let me sum data in one column
if three criteria are met in the other columns. Here is sample data:

A2:A65535 = Dates (looking for all items that = 3/15/2008)
C2:C65535 = Text (looking for all items that equal = "eChannel")
O2:O65535 = Text (looking for all items that equal = "Direct Expense")
AG2:AG65535 = Numbers (I want to sum all numbers if all three criteria above
are met for a particular row)

So, I want to sum column AG for all rows that have 3/15/2008 in column A,
"echannel" in column C, and "Direct expense" in column O.

I've tried Ctrl-Shift-Enter formulas but I must not have them correct. Also
I've tried SUMPRODUCT formulas but here again I must be doing something
wrong. Thanks in advance for any help.

 

[PCLIVE]

Your date will be a serial number (39522).
=SUMPRODUCT(--(A2:A65535=39522),--(C2:C65535="eChannel"),--(O2:O65535="Direct
Expense"),AG2:AG65535)

If criteria to be met will reside in cells, then you can replace with the
cell references.
=SUMPRODUCT(--(A2:A65535=A1),--(C2:C65535=C1),--(O2:O65535=O1),AG2:AG65535)

HTH,
Paul

 

[rdbjr99]

Paul: Thanks but when I enter the formula you provided I get #N/A returned.
Does it make a difference that the data is in one spreadsheet and the formula
is in another? For example I am trying to sum the data in the Mar. Scorecard
worksheet and the data is in the FCByAccount worksheet. Here is what my
formula looks like:
=SUMPRODUCT(--(FCbyAccount!A2:A65535=39522),--(FCbyAccount!C2:C65535="eChannel"),--(FCbyAccount!O2:O65535="Direct
Expense"),FCbyAccount!AG2:AG65535)

 

[David Biddulph]

To avoid converting the date separately to its serial number, you could
change
=SUMPRODUCT(--(A2:A65535=DATE(2008,3,15)),--(C2:C65535="eChannel"),--(O2:O65535="Direct
Expense"),AG2:AG65535)
to
=SUMPRODUCT(--(A2:A65535=39522),--(C2:C65535="eChannel"),--(O2:O65535="Direct
Expense"),AG2:AG65535)

 

[PCLIVE]

That should be fine. However, if you have any errors in your data (ex.
#DIV/0!, #N/A, etc.), then your formula will also result in an error.

Regards,
Paul

--