Sumifs with mod

[Ms_Black]

Hi,

I'm an Excel newbie and in a bit of a pickle.

I'm trying to find the sum of a range of cells (A1:C12) for numbers between 20 to 80, but only ones that are even numbers.

I'd normally do this with a SUMIFS function easily, but I have no idea how to narrow it down to just the even numbers. I'd wager with a MOD function, but I don't know how to *ahem* formulate my formula

Any tips?

Thanks in advance!

 

[admiral100]

Hi,

Try this:

=SUMPRODUCT(A1:C12,--(A1:C12>=20),--(A1:C12<=80),--(MOD(A1:C12,2)=0))

 

[Ms_Black]

Hi,

Try this:

=SUMPRODUCT(A1:C12,--(A1:C12>=20),--(A1:C12<=80),--(MOD(A1:C12,2)=0))

Thank you so much! This worked wonderfully.

Follow-up question, will this still work if I use it on a range where some cells have text in them? I have certain sheets where the data is a tad jumbled, and I'd only like to sum the cells with numbers in them. Though I don't know if adding an IF clause would complicate it too much, perhaps I'd be better off trying my luck with VBA - Excel sure is fascinating!

 

[Aladin Akyurek]

Thank you so much! This worked wonderfully.

Follow-up question, will this still work if I use it on a range where some cells have text in them? I have certain sheets where the data is a tad jumbled, and I'd only like to sum the cells with numbers in them. Though I don't know if adding an IF clause would complicate it too much, perhaps I'd be better off trying my luck with VBA - Excel sure is fascinating!

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(A1:C12),IF(A1:C12>=20,IF(A1:C12<=80,IF(MOD(A1:C12,2)=0,A1:C12)))))