Conditional summing question

  • Thread starter Thread starter Steve Conway
  • Start date Start date
S

Steve Conway

thanks for your responses:
Each column heading is the "named range"
Col A Col B Col C Col D
program type revclass amount

2207 revenues taxes 100
2207 expenses salary 200
2300 revenues interest300
2300 expenses benefits400
2207 revenues taxes 500

Below was the formula I used, I have never used sumproduct
before-where did I go wrong??
=SUMPRODUCT(program="2207")*(type="revenues")*
(revclass="taxes")*(amount)
Based on responses to my original question about
conditional summing, I got an
error message of #VALUE, above is my data using named
ranges.
thanks again everyone!!
#VALUE

I am trying to get the answer of tax revenue in program
2207 =$600
 
Missing parens to start.

=SUMPRODUCT((A1:A100="2207")*(B1:B100="revenues")*(C1:C100="taxes")*D1:D100)

Also, unless your revenues are ever benefits, you don't need to specify
"taxes" as your revenues are always taxes. You could simply:

=SUMPRODUCT((A1:A100="2207")*(B1:B100="revenues")*D1:D100)

-Jesse
 
Its an array formula so you need to enter as an array
enter it which is ctrl-shift-enter not enter.
 
Sumproduct, while an array formula, does not need to be entered with
CTRL-SHIFT-ENTER.
 
You might need to remove the double quotes around 2207...

=SUMPRODUCT((program=2207)*(type="revenues")*(revclass="taxes"),amount)

and the named ranges must be of the same size.
 
Here is what worked after taking in all your comments!
Steve
=SUMPRODUCT((program=2207)*(type="revenues")*
(revclass="taxes")*(amount))
Whaallaa! $600 the correct answer!!
 
Back
Top