Hi folks,

1st post so please be gentle!

I am after a formula that works in the same way as a Sumif.


I am trying to count particular data for a certian week. However this data is in more than 1 column!

Currently we just add multiple SUMIF's together, but I'm sure there must be a way to add a certain amount of columns together.

Example:

Current way of doing it:
=SUMIF(Master_Data!$C:$C,D38,Master_Data!$CY:$CY)+ SUMIF(Master_Data!$C:$C,D38,Master_Data!$CZ:$CZ)+S UMIF(Master_Data!$C:$C,D38,Master_Data!$DA:$DA)

Is there another formula that will add up all these neighboring columns for a particular week?

I know you can't do it with columns that are seperated but when the columns are in order eg A,B,C,D,E I figure there must be something that will do it!

Please help as we have been working this way for 2 years and I'm convinced there is an easier and tidier way of doing this


Cheers
Chris

If you're using xl2007, you can use the entire column. But before xl2007, you
have to limit your range.

But you could try:

=sumproduct((Master_Data!$C1:$C999=D38)*(Master_Da ta!$CY1:$da999))






ODB wrote:

Hi folks,

1st post so please be gentle!

I am after a formula that works in the same way as a Sumif.

I am trying to count particular data for a certian week. However this
data is in more than 1 column!

Currently we just add multiple SUMIF's together, but I'm sure there
must be a way to add a certain amount of columns together.

EXAMPLE:

-Current way of doing it:-
=SUMIF(Master_Data!$C:$C,D38,Master_Data!$CY:$CY)+ SUMIF(Master_Data!$C:$C,D38,Master_Data!$CZ:$CZ)+S UMIF(Master_Data!$C:$C,D38,Master_Data!$DA:$DA)

Is there another formula that will add up all these neighboring columns
for a particular week?

I know you can't do it with columns that are seperated but when the
columns are in order eg A,B,C,D,E I figure there must be something that
will do it!

Please help as we have been working this way for 2 years and I'm
convinced there is an easier and tidier way of doing this

Cheers
Chris

--
ODB

--

Dave Peterson

If I were doing this, I'd use a helper column that adds the values in CY, CZ,
DA, ...

Then use that in the =sumif() formula.

I bet it would recalculate faster (just a gut feeling).

ODB wrote:

Hi folks,

1st post so please be gentle!

I am after a formula that works in the same way as a Sumif.

I am trying to count particular data for a certian week. However this
data is in more than 1 column!

Currently we just add multiple SUMIF's together, but I'm sure there
must be a way to add a certain amount of columns together.

EXAMPLE:

-Current way of doing it:-
=SUMIF(Master_Data!$C:$C,D38,Master_Data!$CY:$CY)+ SUMIF(Master_Data!$C:$C,D38,Master_Data!$CZ:$CZ)+S UMIF(Master_Data!$C:$C,D38,Master_Data!$DA:$DA)

Is there another formula that will add up all these neighboring columns
for a particular week?

I know you can't do it with columns that are seperated but when the
columns are in order eg A,B,C,D,E I figure there must be something that
will do it!

Please help as we have been working this way for 2 years and I'm
convinced there is an easier and tidier way of doing this

Cheers
Chris

--
ODB

--

Dave Peterson