How to use Sumifs with vertically and horizontally criterias

[William G]

Hi,

I am trying to sum a range of numbers that have a few criterias that
are both vertical and horizontal. Here is a small sample of my
layout:

a b c d e
Item 1 Item 2 Item 1 Item 3 FS Type
1 50.00 25.00 150
2 1200.00 800.00 150
3 60.00 155
4 150.00 150
5 75.00 150
6 50.00 165

I need the FS Type 150 to sum all the items. In this sample, I should
get the following answers:
Item 1 / 150 = 1325.00
Item 2 / 150 = 175.00
Item 3 / 150 = 800.00

I notice that sumifs only works horitzonal or vertical, unable to
combine both; unless I'm missing something. What formula (or array)
can I use (or a combination of formulas) to pull this data as listed
in the sample? I am unfamilar with 'array' function, but open for all
suggestions (I want to avoid using any macros or visual basic
applications).

Thank you for your time!!!
William

 

[isabelle]

hi William,

i have not fully understand, maybe:

=SUMPRODUCT(--($A$2:$A$10)+($C$2:$C$10)*($E$2:$E$10=150))
=SUMPRODUCT(--($B$2:$B$10)*($E$2:$E$10=150))
=SUMPRODUCT(--($D$2:$D$10)*($E$2:$E$10=150))

isabelle

Le 2013-03-07 17:48, William G a écrit :

 

[joeu2004]

I notice that sumifs only works horitzonal or vertical,
unable to combine both; unless I'm missing something.

That is not exactly correct.

It is true that for SUMIF(range1,criteria,range2), range1 and range2 should
have the __same__ dimensions. If both are 2-dimensional, SUMIF will sum
over both dimenstions.

But if range1 or range2 is 1-dimensional, SUMIF will sum over the longest
dimension, IIRC.

a b c d e
Item 1 Item 2 Item 1 Item 3 FS Type
1 50.00 25.00 150
2 1200.00 800.00 150
3 60.00 155
4 150.00 150
5 75.00 150
6 50.00 165

I need the FS Type 150 to sum all the items. In this sample,
I should get the following answers:
Item 1 / 150 = 1325.00
Item 2 / 150 = 175.00
Item 3 / 150 = 800.00

Assuming you mislabeld the row numbers, and the line with "Item 1" is
actually row 1:

=SUMPRODUCT((A11="Item 1")*(E2:E7=150)*A27)
=SUMPRODUCT((A11="Item 2")*(E2:E7=150)*A27)
=SUMPRODUCT((A11="Item 3")*(E2:E7=150)*A27)

 

[William G]

Assuming you mislabeld the row numbers, and the line with "Item 1" is
actually row 1:

=SUMPRODUCT((A11="Item 1")*(E2:E7=150)*A27)
=SUMPRODUCT((A11="Item 2")*(E2:E7=150)*A27)
=SUMPRODUCT((A11="Item 3")*(E2:E7=150)*A27)

This formula works perfect!!!! THANK YOU!!!!!!