Forum Discussion
Need help w/ SUMIF
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 https://www.extendoffice.com/documents/excel/1439-excel-count-sum-odd-even-numbers-rows.html which gives a similar solution.
I hope that makes sense
Regards
Hello
Yes i agree w/ u ITS IMPOSSIBLE TO USE SUMIF FOR THIS EXERCISE, anyways tho, i will follow your advice and use SUMPRODUCT. Same here, i've looked everywhere and found nothing