SUMIF Function not working Horizontally

[Req]

Hi,

I've tried many different variations to my problem and it seems as
though SUMIF doesn't work in a horizontal table.

Could someone please clarify this?

Cheers

 

[Katherine Coombs]

the range can be horizontal or vertical - can you post your formula and some
data to give us an idea of where it might be going wrong?

 

[Req]

Sure,


A1 Project Name 1
A2 Project Name 2
A3 Project Name 3
A4 Project Name 1

B1 C1 D1 E1
3 3.5 6 3

=SUMIF(A1:A4,"Project Name 1",B1:E1)

 

[Wild Bill]

If you're trying to sum rows of a rectangle, I am not aware of this
capability with SUMIF. To my knowledge it only returns the results from
a single column or row. Maybe the magic Aladin could make it work and
maybe a clever use of SUM would. Anyway, if you add a column to the
right of the rectangle that sums the values to the left for each row,
and use that for your sum range for SUMIF, you would have the sum from
the rectangle for every row that matches.

If I misunderstood, don't worry, it looks like you're in capable hands.

 

[Req]

To make it really clear, this is exactly what I'm attempting.

1 A B C
D
2 Names 01/08 02/08
03/08
3 Name One 4 3
2
4 Name Two 5 1
7
5 Name Two 6 4
2
6 Name Three 12 4
3

The sum I want to do is this:

If A3:A6 is equal to "Name Two", add the contents of Column B, C and D
to the end result. This means that the result should be coming up as
25. Instead with my earlier formula, I'm getting the result '11'. It's
not calculating the rectangle; it only seems to calculate the first
column.

Thanks,
-Req

 

[Req]

Sorry, your SUMPRODUCT approach didn't help my problem

 

[Wild Bill]

Maybe the magic Aladin could make it work and
maybe a clever use of SUM would.

And in steps Alan Beban to the rescue with SUMPRODUCT multiplying the
values of Booleans

Use his approach and save yourself from having the extra column.

 

[Ron Rosenfeld]

To make it really clear, this is exactly what I'm attempting.

1 A B C
D
2 Names 01/08 02/08
03/08
3 Name One 4 3
2
4 Name Two 5 1
7
5 Name Two 6 4
2
6 Name Three 12 4
3

The sum I want to do is this:

If A3:A6 is equal to "Name Two", add the contents of Column B, C and D
to the end result. This means that the result should be coming up as
25. Instead with my earlier formula, I'm getting the result '11'. It's
not calculating the rectangle; it only seems to calculate the first
column.

Thanks,
-Req

Label Column A -- Names (e.g. A3:A6)
Label the range where you have your Data -- DateData (e.g. B36)
Array-enter:

=SUM((Names="Name Two")*DateData)

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

In the formula, you can substitute a cell reference for "Name Two". This would
make looking at Name One and other names easier.

Note the better answer you obtain with a more complete description of your
problem.


--ron