sum if function
 

On one worksheet I have an excel data base with alpha/numeric entries and
some blank cells with each data listing on a row through multiple columns.
On another worksheet I have a report format for monthly revenue reports.
I want to reference the data entry in the report to the data base as its
source.
I need to add all entries that have four common criteria eg. month, year,
code,sub code to give a subtotal of revenue for that month for the selected
criteria.
Each list of common criteria are in a separate column for each data entry.
A shortened version of my formula for only two criteria looking for code 43
and subcode 2 is:
=sum(if((a2:a3000="43")*(c2:c3000="2"),h2:h3000))
I am getting an answer $0.00 instead of $574.28.

sum if function
 

Try removing the quotes from around any numbers:

Array entered** :

=SUM(IF((A2:A3000=43)*(C2:C3000=2),H2:H3000))

Or, this normally entered version:

=SUMPRODUCT(--(A2:A3000=43),--(C2:C3000=2),H2:H3000)

It's better if you use cells to hold the criteria:

J2 = 43
K2 = 2

=SUM(IF((A2:A3000=J2)*(C2:C3000=K2),H2:H3000))

=SUMPRODUCT(--(A2:A3000=J2),--(C2:C3000=K2),H2:H3000)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"james f" <james wrote in message
...
On one worksheet I have an excel data base with alpha/numeric entries and
some blank cells with each data listing on a row through multiple columns.
On another worksheet I have a report format for monthly revenue reports.
I want to reference the data entry in the report to the data base as its
source.
I need to add all entries that have four common criteria eg. month, year,
code,sub code to give a subtotal of revenue for that month for the
selected
criteria.
Each list of common criteria are in a separate column for each data entry.
A shortened version of my formula for only two criteria looking for code
43
and subcode 2 is:
=sum(if((a2:a3000="43")*(c2:c3000="2"),h2:h3000))
I am getting an answer $0.00 instead of $574.28.