• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

G

giri ravirrala

New Member
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.
 

Attachments

  • sum(sumifs).xlsx
    9.6 KB · Views: 4
giri ravirrala said:
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.
Click to expand...
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
 
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
 
You must log in or register to reply here.
Back
Top