Results 1 to 9 of 9
Thread: Sum based on Value (Excel 2003)

20081123, 01:33 #1
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sum based on Value (Excel 2003)
Hi all
I can use =SUMIF(A1:A6,"=2") to sum all 2 in a range.
Can SUM(IF....) do the same ? I can't get the formula to work
cheersHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081123, 08:29 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sum based on Value (Excel 2003)
It's not clear what you want. Jerry has posted one possible solution. Depending on what you want to accomplish, you might use the following array formula (confirm with Ctrl+Shift+Enter) instead:
=SUM(IF(A1:A6=2,A1:A6))

20081123, 08:31 #3
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Sum based on Value (Excel 2003)
You could use:
=COUNTIF(A1:A6,2)*2Jerry

20081123, 15:03 #4
 Join Date
 Sep 2008
 Posts
 49
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum based on Value (Excel 2003)
SUMIF needs three arguements.
Perhaps =SUMIF(A1:A6, 2, A1:A6) will work for you.
(The =COUNTIF(A1:A6,2)*2 is possibly faster)

20081123, 18:57 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sum based on Value (Excel 2003)
If the range to test the condition on and the range to sum are equal, you can omit the third argument of SUMIF  see SUMIF.

20081124, 03:45 #6
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum based on Value (Excel 2003)
Thanks to all.
How does the =COUNTIF(A1:A6,2)*2 works?
cheersHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081124, 04:01 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sum based on Value (Excel 2003)
The original formula
=SUMIF(A1:A6,"=2")
adds the cells in the range A1:A6 whose value equals 2. If there is 1 such cell, the sum is 2, if there are 2 such cells, their sum is 2*2 = 4, if there are 3 such cells, their sum is 3*2 = 6 etc.
In other words, the sum is 2 times the number of cells that equal 2. COUNTIF calculates that number.
If you had wanted to sum the cells whose value equals 37, the sum would be 37 times the number of cells that equal 37, or
=COUNTIF(A1:A6,37)*37

20081124, 05:25 #8
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum based on Value (Excel 2003)
Hi Hans
Great explaination! Thanks
cheersHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20081124, 13:33 #9
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Sum based on Value (Excel 2003)
1] COUNTIF can use in this way :
=SUM(COUNTIF(A1:A6,2)*{1,1})
2] Or, using SUMIF, and SUMPRODUCT function
=SUMIF(A1:A6,2)
=SUMPRODUCT((A1:A6=2)*A1:A6)
Please take a look of this :
SUMIF can accept the whole Column A, but SUMPRODUCT cannot.