|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 14th, 2003, 08:07 PM
|
|||
|
|||
Conditional summing question
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 
|
|
#2
November 14th, 2003, 08:21 PM
|
|||
|
|||
|
Conditional summing question
Missing parens to start.
=SUMPRODUCT((A1:A100="2207")*(B1:B100="revenues")* (C1:C100="taxes")*D1  100)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 100)-Jesse "Steve Conway" wrote in message ... 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
|
|
#3
November 14th, 2003, 08:25 PM
|
|||
|
|||
|
Conditional summing question
Its an array formula so you need to enter as an array
enter it which is ctrl-shift-enter not enter. -----Original Message----- 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 .
|
|
#4
November 14th, 2003, 08:53 PM
|
|||
|
|||
|
Conditional summing question
Sumproduct, while an array formula, does not need to be entered with
CTRL-SHIFT-ENTER. In article , "Ozzie" wrote: Its an array formula so you need to enter as an array enter it which is ctrl-shift-enter not enter.
|
|
#5
November 14th, 2003, 09:20 PM
|
|||
|
|||
|
Conditional summing question
Thanks for clearing that up. Appreciate it very much.
-----Original Message----- Sumproduct, while an array formula, does not need to be entered with CTRL-SHIFT-ENTER. In article , "Ozzie" wrote: Its an array formula so you need to enter as an array enter it which is ctrl-shift-enter not enter. .
|
|
#6
November 14th, 2003, 09:27 PM
|
|||
|
|||
|
Conditional summing question
Jesse - something still not right. Tried this out and
resultant was zero. -----Original Message----- Missing parens to start. =SUMPRODUCT((A1:A100="2207")*(B1:B100="revenues") * (C1:C100="taxes")*D1 100)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 100)-Jesse "Steve Conway" wrote in message ... 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 .
|
|
#7
November 14th, 2003, 09:53 PM
|
|||
|
|||
|
Conditional summing question
You might need to remove the double quotes around 2207...
=SUMPRODUCT((program=2207)*(type="revenues")*(revc lass="taxes"),amount) and the named ranges must be of the same size. "Steve Conway" wrote in message ... 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
|
|
#8
November 14th, 2003, 10:50 PM
|
|||
|
|||
|
Conditional summing question
Here is what worked after taking in all your comments!
Steve =SUMPRODUCT((program=2207)*(type="revenues")* (revclass="taxes")*(amount)) Whaallaa! $600 the correct answer!! -----Original Message----- Jesse - something still not right. Tried this out and resultant was zero. -----Original Message----- Missing parens to start. =SUMPRODUCT((A1:A100="2207")*(B1:B100="revenues" )* (C1:C100="taxes")*D1 100)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 100)-Jesse "Steve Conway" wrote in message ... 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 . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
