# How to use Sumifs with vertically and horizontally criterias

W

#### 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
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).

William

I

#### 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 :

J

#### joeu2004

William G said:
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.

William G said:
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)

W

#### 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!!!!!!