Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SumIf function
EENF EPDA EKHC GGEN
Expenditure 1 2 3 4 Income 2 2 2 6 Capital additions 5 2 4 5 Income 7 8 9 5 Capital additions 10 11 12 11 How can I total the more than one column if the criteria is "Income"? |
#2
|
|||
|
|||
=sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Inco me"),C2:C20)
-- HTH RP (remove nothere from the email address if mailing direct) "nc" wrote in message ... EENF EPDA EKHC GGEN Expenditure 1 2 3 4 Income 2 2 2 6 Capital additions 5 2 4 5 Income 7 8 9 5 Capital additions 10 11 12 11 How can I total the more than one column if the criteria is "Income"? |
#3
|
|||
|
|||
Thanks Bob.
I have about 10 columns. I was looking for a symplified function. "Bob Phillips" wrote: =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Inco me"),C2:C20) -- HTH RP (remove nothere from the email address if mailing direct) "nc" wrote in message ... EENF EPDA EKHC GGEN Expenditure 1 2 3 4 Income 2 2 2 6 Capital additions 5 2 4 5 Income 7 8 9 5 Capital additions 10 11 12 11 How can I total the more than one column if the criteria is "Income"? |
#4
|
|||
|
|||
Then add one more column that sums across the row, then use one of Bob's
SUMIF() functions and reference the new column "nc" wrote: Thanks Bob. I have about 10 columns. I was looking for a symplified function. "Bob Phillips" wrote: =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Inco me"),C2:C20) -- HTH RP (remove nothere from the email address if mailing direct) "nc" wrote in message ... EENF EPDA EKHC GGEN Expenditure 1 2 3 4 Income 2 2 2 6 Capital additions 5 2 4 5 Income 7 8 9 5 Capital additions 10 11 12 11 How can I total the more than one column if the criteria is "Income"? |
#5
|
|||
|
|||
Here is a formula with a few columns, add the rest
=SUMPRODUCT(--(A2:A20="Income"),B2:B20+C2:C20+D2:D20) -- HTH RP (remove nothere from the email address if mailing direct) "nc" wrote in message ... Thanks Bob. I have about 10 columns. I was looking for a symplified function. "Bob Phillips" wrote: =sumif(A2:A20="Income"),B2:B20)+sumif(A2:A20="Inco me"),C2:C20) -- HTH RP (remove nothere from the email address if mailing direct) "nc" wrote in message ... EENF EPDA EKHC GGEN Expenditure 1 2 3 4 Income 2 2 2 6 Capital additions 5 2 4 5 Income 7 8 9 5 Capital additions 10 11 12 11 How can I total the more than one column if the criteria is "Income"? |
#6
|
|||
|
|||
Try...
=SUMPRODUCT((A2:A6="Income")*(B2:E6)) Adjust the range accordingly. Hope this helps! In article , "nc" wrote: EENF EPDA EKHC GGEN Expenditure 1 2 3 4 Income 2 2 2 6 Capital additions 5 2 4 5 Income 7 8 9 5 Capital additions 10 11 12 11 How can I total the more than one column if the criteria is "Income"? |
#7
|
|||
|
|||
Thanks Domenic.
Can this function be adapted to use more than one criteria. "Domenic" wrote: Try... =SUMPRODUCT((A2:A6="Income")*(B2:E6)) Adjust the range accordingly. Hope this helps! In article , "nc" wrote: EENF EPDA EKHC GGEN Expenditure 1 2 3 4 Income 2 2 2 6 Capital additions 5 2 4 5 Income 7 8 9 5 Capital additions 10 11 12 11 How can I total the more than one column if the criteria is "Income"? |
#8
|
|||
|
|||
Sure...
=SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterio n")*(RangeC="Criterion" )*(RangeToSum)) Remove the quotes if your criterion is a numerical value. Hope this helps! In article , "nc" wrote: Thanks Domenic. Can this function be adapted to use more than one criteria. |
#9
|
|||
|
|||
Domenic wrote...
Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on") *(RangeC="Criterion")*(RangeToSum)) .... I'm not an absolutist about using separate arguments for all criteria arguments, but the value array should be a separate argument in conditional sums (as opposed to conditional counts), i.e., =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterio n") *(RangeC="Criterion"),RangeToSum) because SUMPRODUCT will ignore entries in RangeToSum that aren't numeric and can't be converted to numeric *IF* RangeToSum were a separate argument. In that situation, your formula would return #VALUE!. |
#10
|
|||
|
|||
Thanks Harlan! The reason I didn't use a separate argument here is that
the range actually spans a number of columns. I should have made that clear... :) Thanks again, Harlan! In article . com, "Harlan Grove" wrote: Domenic wrote... Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on") *(RangeC="Criterion")*(RangeToSum)) ... I'm not an absolutist about using separate arguments for all criteria arguments, but the value array should be a separate argument in conditional sums (as opposed to conditional counts), i.e., =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterio n") *(RangeC="Criterion"),RangeToSum) because SUMPRODUCT will ignore entries in RangeToSum that aren't numeric and can't be converted to numeric *IF* RangeToSum were a separate argument. In that situation, your formula would return #VALUE!. |
#11
|
|||
|
|||
But in some (many) situations, that error return would be a welcomed trap,
denoting contaminated data. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Harlan Grove" wrote in message ups.com... Domenic wrote... Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on") *(RangeC="Criterion")*(RangeToSum)) ... I'm not an absolutist about using separate arguments for all criteria arguments, but the value array should be a separate argument in conditional sums (as opposed to conditional counts), i.e., =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterio n") *(RangeC="Criterion"),RangeToSum) because SUMPRODUCT will ignore entries in RangeToSum that aren't numeric and can't be converted to numeric *IF* RangeToSum were a separate argument. In that situation, your formula would return #VALUE!. |
#12
|
|||
|
|||
Very true...
In article , "RagDyer" wrote: But in some (many) situations, that error return would be a welcomed trap, denoting contaminated data. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Harlan Grove" wrote in message ups.com... Domenic wrote... Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criteri on") *(RangeC="Criterion")*(RangeToSum)) ... I'm not an absolutist about using separate arguments for all criteria arguments, but the value array should be a separate argument in conditional sums (as opposed to conditional counts), i.e., =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterio n") *(RangeC="Criterion"),RangeToSum) because SUMPRODUCT will ignore entries in RangeToSum that aren't numeric and can't be converted to numeric *IF* RangeToSum were a separate argument. In that situation, your formula would return #VALUE!. |
#13
|
|||
|
|||
Hi Domenic
I tried using the following function, =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")* (B2:D6)) with the table below and I get a value of zero. I was expecting 1200. Jan Feb March Dividend 100 100 100 Interest 100 100 100 Expenses -100 -100 -100 Dividend 100 100 100 Interest 100 100 100 Thanks. "Domenic" wrote: Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterio n")*(RangeC="Criterion" )*(RangeToSum)) Remove the quotes if your criterion is a numerical value. Hope this helps! In article , "nc" wrote: Thanks Domenic. Can this function be adapted to use more than one criteria. |
#14
|
|||
|
|||
Hi,
Try this: =SUMPRODUCT(((A2:A6="Dividend")+(A2:A6="Interest") 0)*(B2:D6)) Your formula basically says sum if a cell is equal to "Dividend" AND "Interest" (which isn't possible) while it should say sum if a cell is equal to "Dividend" OR "Interest" Regards, KL "nc" wrote in message ... Hi Domenic I tried using the following function, =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")* (B2:D6)) with the table below and I get a value of zero. I was expecting 1200. Jan Feb March Dividend 100 100 100 Interest 100 100 100 Expenses -100 -100 -100 Dividend 100 100 100 Interest 100 100 100 Thanks. "Domenic" wrote: Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criterio n")*(RangeC="Criterion" )*(RangeToSum)) Remove the quotes if your criterion is a numerical value. Hope this helps! In article , "nc" wrote: Thanks Domenic. Can this function be adapted to use more than one criteria. |
#15
|
|||
|
|||
Actually, the following should suffice...
=SUMPRODUCT(((A2:A6="Dividend")+(A2:A6="Interest") )*(B2:D6)) or =SUMPRODUCT(((A2:A6=F2)+(A2:A6=G2))*(B2:D6)) ....where F2 contains your first criterion, such as Dividend, and G2 contains your second criterion, such as Interest. Hope this helps! In article , "KL" wrote: Hi, Try this: =SUMPRODUCT(((A2:A6="Dividend")+(A2:A6="Interest") 0)*(B2:D6)) Your formula basically says sum if a cell is equal to "Dividend" AND "Interest" (which isn't possible) while it should say sum if a cell is equal to "Dividend" OR "Interest" Regards, KL |
#16
|
|||
|
|||
Why not create an additional column, say E, that sums monthly values
like in: E2, copied down: =SUM(B2:D2) which allows you to avoid expensive conditional calculations? nc wrote: Hi Domenic I tried using the following function, =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")* (B2:D6)) with the table below and I get a value of zero. I was expecting 1200. Jan Feb March Dividend 100 100 100 Interest 100 100 100 Expenses -100 -100 -100 Dividend 100 100 100 Interest 100 100 100 Thanks. "Domenic" wrote: Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criter ion")*(RangeC="Criterion" )*(RangeToSum)) Remove the quotes if your criterion is a numerical value. Hope this helps! In article , "nc" wrote: Thanks Domenic. Can this function be adapted to use more than one criteria. |
#17
|
|||
|
|||
Most definitely...
In article , Aladin Akyurek wrote: Why not create an additional column, say E, that sums monthly values like in: E2, copied down: =SUM(B2:D2) which allows you to avoid expensive conditional calculations? nc wrote: Hi Domenic I tried using the following function, =SUMPRODUCT((A2:A6="Dividend")*(A2:A6="Interest")* (B2:D6)) with the table below and I get a value of zero. I was expecting 1200. Jan Feb March Dividend 100 100 100 Interest 100 100 100 Expenses -100 -100 -100 Dividend 100 100 100 Interest 100 100 100 Thanks. "Domenic" wrote: Sure... =SUMPRODUCT((RangeA="Criterion")*(RangeB="Criter ion")*(RangeC="Criterion" )*(RangeToSum)) Remove the quotes if your criterion is a numerical value. Hope this helps! In article , "nc" wrote: Thanks Domenic. Can this function be adapted to use more than one criteria. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
SUMIF function | Excel Worksheet Functions | |||
help with "criterea" in the sumif function | Excel Worksheet Functions |