SUMIF like formulae?? Any ideas?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ODB - 20 Mar 2008 15:06 GMT

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 thi
data is in more than 1 column!

Currently we just add multiple SUMIF's together, but I'm sure ther
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)+SUMIF(Master_Data!$C:$C,D38,Master_Data!$DA:$DA)

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

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

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

Cheers
Chri

--
ODB

Reply to this Message

Dave Peterson - 20 Mar 2008 22:17 GMT

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_Data!$CY1:$da999))

> Hi folks,
>
[quoted text clipped - 28 lines]
> --
> ODB

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 20 Mar 2008 22:20 GMT

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).

> Hi folks,
>
[quoted text clipped - 28 lines]
> --
> ODB

Signature

Dave Peterson

Reply to this Message