Forum Discussion
Need help w/ SUMIF
Hi it's for a class, I need to sum all the even numbers w/ the function SUMIF, to explain better
1-2-3-4-5-6-7-8-9-0
That's a list of random numbers, and i need to sum all EVEN numbers, so the result will be = 12, can
someone tell me or even better give me the function to do it? i know how sumif works but the condition i dont seem to get it right, i know that even numbers have a residue of 0. Thanks a lot
Hi,
I would suggest this formula:
=SUMPRODUCT((MOD(O1:X1,2)=0)*O1:X1)
But you need each number to separated in a single cell.
If you have another class quiz asking you to sum the odd numbers only, you can simply adjust just the formula as follows:
=SUMPRODUCT((MOD(O1:X1,2)<>0)*O1:X1)
Hope that helps
Hi,
I would suggest this formula:
=SUMPRODUCT((MOD(O1:X1,2)=0)*O1:X1)
But you need each number to separated in a single cell.
If you have another class quiz asking you to sum the odd numbers only, you can simply adjust just the formula as follows:
=SUMPRODUCT((MOD(O1:X1,2)<>0)*O1:X1)
Hope that helps
Thanks a lot for your time
I did realize that i needed to make 2 separate cells, one with the main range (Mod2=0) if cell=0 is even if not its uneven, and the sum range of the numbers that i wanted to sum, anyways i did get it to work, i really appreciate it, but the task was to use only sumif, allthough using sumproduct is more effective and more reasonable
As i said before thanks for your time and attention
In fact, I have tried to achieve that using SUMIF, but with no success!
SUMIF is not as capable as SUMPRODUCT!
I don't think that this can be done with SUMIF, because this solution requires an array handler to filter out the odd numbers and sum the even numbers only, and this is what SUMPRODUCT does.
SUMPRODUCT will filter out the odd number by converting them to zeros, then sum resultant array.
This part of the formula (MOD(O1:X1,2)=0) will give us an array of boolean values Trues (1) and Falses (0) indicates if the cell's value is even or not.
This is how the formula looks like when you calculate each part of it:
Then we will multiply each boolean value with the corresponding number in the range to filter out the odd numbers as the below screenshot:
I have also searched the Internet for a solution using SUMIF, but found nothing!
Please check out this link which gives a similar solution.
I hope that makes sense
Regards
