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

[joeu2004]

"William G" <wcg...@cox.net> wrote:
> 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" <wcg...@cox.net> wrote:
> 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((A1:D1="Item 1")*(E2:E7=150)*A2:D7)
=SUMPRODUCT((A1:D1="Item 2")*(E2:E7=150)*A2:D7)
=SUMPRODUCT((A1:D1="Item 3")*(E2:E7=150)*A2:D7)

[William G]

On Mar 7, 7:05 pm, "joeu2004" <joeu2...@foo.bar> wrote:

> Assuming you mislabeld the row numbers, and the line with "Item 1" is
> actually row 1:
>
> =SUMPRODUCT((A1:D1="Item 1")*(E2:E7=150)*A2:D7)
> =SUMPRODUCT((A1:D1="Item 2")*(E2:E7=150)*A2:D7)
> =SUMPRODUCT((A1:D1="Item 3")*(E2:E7=150)*A2:D7)

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