I need to sum multiple columns based on the value in one column and
the excel formula I used below always evaluates to 0 and I can't
understand why?

=SUMIF($AC$155:$AC$334,C62,$AM$155:$AR$334)

Here's some sample data assuming c62 = 1

AC AM AN AO AP AQ AR
Year ...... Cost 1 Cost 2 Cost 3 Cost 4 Cost 5 Cost 6
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00

Thanks for any input.

AJ

try this idea
=SUMPRODUCT(($F$2:$F$33=$D$1)*$G$2:$R$33)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AdmiralAJ" wrote in message
...
I need to sum multiple columns based on the value in one column and
the excel formula I used below always evaluates to 0 and I can't
understand why?

=SUMIF($AC$155:$AC$334,C62,$AM$155:$AR$334)

Here's some sample data assuming c62 = 1

AC AM AN AO AP AQ AR
Year ...... Cost 1 Cost 2 Cost 3 Cost 4 Cost 5 Cost 6
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00

Thanks for any input.

AJ

Try: =SUMPRODUCT(($AC$155:$AC$334=C62)*$AM$155:$AR$334)

Albeit Excel accepts the SUMIF formula
with the multi-col sum range: $AM$155:$AR$334,
which leads one to wrongly think that it also works like that,
in reality, only the leftmost col: $AM$155:$AR$334 gets summed
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AdmiralAJ" wrote in message
...
I need to sum multiple columns based on the value in one column and
the excel formula I used below always evaluates to 0 and I can't
understand why?

=SUMIF($AC$155:$AC$334,C62,$AM$155:$AR$334)

Here's some sample data assuming c62 = 1

AC AM AN AO AP AQ AR
Year ...... Cost 1 Cost 2 Cost 3 Cost 4 Cost 5 Cost 6
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00

Thanks for any input.

AJ

Errata, line:
in reality, only the leftmost col: $AM$155:$AR$334 gets summed

should have read as:
in reality, only the leftmost col in the range, ie: $AM$155:$AM$334 gets
summed
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Try this:

=SUMPRODUCT(($AC$155:$AC$334=1)*$AM$155:$AR$334)


--
Biff
Microsoft Excel MVP


"AdmiralAJ" wrote in message
...
I need to sum multiple columns based on the value in one column and
the excel formula I used below always evaluates to 0 and I can't
understand why?

=SUMIF($AC$155:$AC$334,C62,$AM$155:$AR$334)

Here's some sample data assuming c62 = 1

AC AM AN AO AP AQ AR
Year ...... Cost 1 Cost 2 Cost 3 Cost 4 Cost 5 Cost 6
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00

Thanks for any input.

AJ

On Jan 9, 3:47 pm, "T. Valko" wrote:
Try this:

=SUMPRODUCT(($AC$155:$AC$334=1)*$AM$155:$AR$334)

--
Biff
Microsoft Excel MVP

"AdmiralAJ" wrote in message

...

I need to sum multiple columns based on the value in one column and
the excel formula I used below always evaluates to 0 and I can't
understand why?

=SUMIF($AC$155:$AC$334,C62,$AM$155:$AR$334)

Here's some sample data assuming c62 = 1

AC AM AN AO AP AQ AR
Year ...... Cost 1 Cost 2 Cost 3 Cost 4 Cost 5 Cost 6
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 83.61 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
1 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00
2 0.00 92.01 0.00 0.00 0.00 0.00

Thanks for any input.

AJ

Thanks everyone the solution worked great!