sum(sumifs(SUMIFS(sum_range, criteria_range1, criteria1range, [criteria_range2, criteria2])

[giri ravirrala]

Hi
I would like to sum column , providing that certain criteria are met. One of my criteria reference values in a range of cells.
Sum(SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...))
=SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,{50861394,50861765,50861767},$C$4:$C$28,I30)) in I35 cell

could you please help me to use the range H31:H33 instead of typing the value {50861394,50861765,50861767}

Regards,
Giri.

 

[jayalaxmi]

Hi
I would like to sum column , providing that certain criteria are met. One of my criteria reference values in a range of cells.
Sum(SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...))
=SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,{50861394,50861765,50861767},$C$4:$C$28,I30)) in I35 cell

could you please help me to use the range H31:H33 instead of typing the value {50861394,50861765,50861767}

Regards,
Giri.

Hello and good eve

See if this helps u

=SUMPRODUCT((H31=$B$4:$B$28)*($I$30=$C$4:$C$28)*($E$4:$E$28))

Regards
Jaya

 

[giri ravirrala]

thank you jayalaxmi for your reply,

but my requirement is the sum as against the range H31:H33
not the single cell H31

 

[jayalaxmi]

thank you jayalaxmi for your reply,

but my requirement is the sum as against the range H31:H33
not the single cell H31

What is the output expected then??

 

[Nunes]

Hi Giri,

Your formula
=SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,{50861394,50861765,50861767},$C$4:$C$28,I30))

Will work fine if you just replace the hand-typed array {...} by the range reference H31:H33, like this:
=SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,H31:H33,$C$4:$C$28,I30))

You just have to evaluate the formula as an array, ie, after typing the formula you must press Ctrl+Shift>Enter instead of just your typical Enter.

Hope it helps

Best
Nuno

 

[giri ravirrala]

thank you very much , it is really working fine as per my requirement

 

[giri ravirrala]

thank you for help,

i want to emty the cells having "0" , please give your suggestion