Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional sum formula
I have a list of numbers to add if they meet two criteria and I have been
trying to use the conditional sum feature in excel. Column A is a list from 1 to 5 and I want to sum all the numbers in column B if Column A is either a 1 or a 5. Below is the array formula the conditional sum feature produces and it returns 0 when it should return 11. The array works if there is only one variable but not multiple. Can someone tell me why its not working? Thank you. {=SUM(IF($A$3:$A$10=5,IF($A$3:$A$10=1,$A$3:$A$10,0 ),0))} 2 2 5 5 2 2 1 2 5 5 1 2 2 2 1 2 2 2 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional sum formula
Oops, I see my formula adds column A when it should be column B. My mistake!
But the problem is the same. The formula doesn't handle multiple criteria. It came out of the conditional sum add in from excel so I don't understand why it wouldn't. But I am willing to get over that and just write the formula myself. :) (if I knew how) "Todd" wrote: I have a list of numbers to add if they meet two criteria and I have been trying to use the conditional sum feature in excel. Column A is a list from 1 to 5 and I want to sum all the numbers in column B if Column A is either a 1 or a 5. Below is the array formula the conditional sum feature produces and it returns 0 when it should return 11. The array works if there is only one variable but not multiple. Can someone tell me why its not working? Thank you. {=SUM(IF($A$3:$A$10=5,IF($A$3:$A$10=1,$A$3:$A$10,0 ),0))} 2 2 5 5 2 2 1 2 5 5 1 2 2 2 1 2 2 2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional sum formula
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{1,5},0))),B1:B10)
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Todd" wrote in message ... I have a list of numbers to add if they meet two criteria and I have been trying to use the conditional sum feature in excel. Column A is a list from 1 to 5 and I want to sum all the numbers in column B if Column A is either a 1 or a 5. Below is the array formula the conditional sum feature produces and it returns 0 when it should return 11. The array works if there is only one variable but not multiple. Can someone tell me why its not working? Thank you. {=SUM(IF($A$3:$A$10=5,IF($A$3:$A$10=1,$A$3:$A$10,0 ),0))} 2 2 5 5 2 2 1 2 5 5 1 2 2 2 1 2 2 2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional sum formula
=SUMPRODUCT(($A$3:$A$10=5)+($A$3:$A$10=1),$B$3:$B$ 10)
This should do it. HTH -- AP "Todd" a écrit dans le message de ... Oops, I see my formula adds column A when it should be column B. My mistake! But the problem is the same. The formula doesn't handle multiple criteria. It came out of the conditional sum add in from excel so I don't understand why it wouldn't. But I am willing to get over that and just write the formula myself. :) (if I knew how) "Todd" wrote: I have a list of numbers to add if they meet two criteria and I have been trying to use the conditional sum feature in excel. Column A is a list from 1 to 5 and I want to sum all the numbers in column B if Column A is either a 1 or a 5. Below is the array formula the conditional sum feature produces and it returns 0 when it should return 11. The array works if there is only one variable but not multiple. Can someone tell me why its not working? Thank you. {=SUM(IF($A$3:$A$10=5,IF($A$3:$A$10=1,$A$3:$A$10,0 ),0))} 2 2 5 5 2 2 1 2 5 5 1 2 2 2 1 2 2 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum Formula? | Excel Discussion (Misc queries) | |||
need a conditional formula to generate numbers divisible by 4 between a given starting no. & end No. | Excel Worksheet Functions | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
Conditional Formatting formula not acceptable? | Excel Discussion (Misc queries) | |||
Formula Dependant Conditional Formatting | Excel Discussion (Misc queries) |