sum in matrix if conditions

[xlepws]

Hi, I'm trying to sum (numeric) content of matrix if 1st colum is into a certain range.
I was able to do it with letters (e.g. sum if 1st column is A or B) but not with numbers (e.g. sum if 1st column is between 10 and 17)

I'm attaching the very simple file I'm working on..can you please help? table is on the right (then I also have a question for that on the left but I'll save it for later).

Thanks!

[JohnTopley]

Try

=SUMIFS(H2:H9,G2:G9,">9",G2:G9,"<17")

Range must be same dimensions so cannot have H2:I9

[AliGW]

Try this:

=SUMPRODUCT($H$2:$I$9*($G$2:$G$9>9)*($G$2:$G$9<17))

This does not need C+S+E.

Also, in E2 copied down:

=SUMPRODUCT($B$2:$C$9*($A$2:$A$9=D2))

[Root_]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IF((G2:G9>9)*(G2:G9<17),H2:I9))

[bebo021999]

For the right table:

=SUMPRODUCT(($G$2:$G$9>9)*($G$2:$G$9<17)*$H$2:$I$9)

BTW, left one:
E1=SUMPRODUCT((($A$2:$A$9="A")+($A$2:$A$9="B"))*$B$2:$C$9)
E2=SUMPRODUCT(($A$2:$A$9="A")*$B$2:$C$9)
E3=SUMPRODUCT(($A$2:$A$9="B")*$B$2:$C$9)

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[xlepws]

Try

=SUMIFS(H2:H9,G2:G9,">9",G2:G9,"<17")

Range must be same dimensions so cannot have H2:I9

mmh..that must be the reason why the formula can't work..so I can't use it mixing ranges vector / matrix

[xlepws]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IF((G2:G9>9)*(G2:G9<17),H2:I9))

This doesn't work, already tried!

[xlepws]

Try this:

=SUMPRODUCT($H$2:$I$9*($G$2:$G$9>9)*($G$2:$G$9<17))

This does not need C+S+E.

Also, in E2 copied down:

=SUMPRODUCT($B$2:$C$9*($A$2:$A$9=D2))

Well this worked I was missing that additional "multiply" which seemed to be the trick (or rule ^^).
Thank you!

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.