SUMIF Function not working Horizontally

  • Thread starter Thread starter Req
  • Start date Start date
R

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
 
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?
 
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)
 
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.
 
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
 
Maybe the magic Aladin could make it work and
maybe a clever use of SUM would.
Click to expand...

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.
 
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
Click to expand...

Label Column A -- Names (e.g. A3:A6)
Label the range where you have your Data -- DateData (e.g. B3:D6)
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
 
Back
Top