Trying to streamline formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joseph - 01 Apr 2008 19:01 GMT

I'm trying to figure if the following can be streamlined a bit:

SUMPRODUCT((WORKSHEET1!$A$2:$A$1000="JANUARY")*(WORKSHEET1!$H$2:$H$1000="JOB1")*WORKSHEET1!$E$2:$E$1000)

This formula is found on Worksheet2...I also have headings for JANUARY,
MONTHS etc.... & JOB1, JOB2, etc..... categories...Is there a way of
referencing the headings in found in Worksheet2....The worksheet contains, 30
columns & 1000 rows....???
Any ideas would be greatly appreciated...

Reply to this Message

JP - 01 Apr 2008 20:08 GMT

One thing you could do is give workbook-level names to each of those
ranges, for example:

=SUMPRODUCT((MyMonthRange="JANUARY")*(MyJobRange="JO
B1")*MyColumnERange)

Go to Insert>Name>Define and give "WORKSHEET1!$A$2:$A$1000" the name
"MyMonthRange" and so on.

Does that help?

HTH,
JP

> I'm trying to figure if the following can be streamlined a bit:
>
[quoted text clipped - 5 lines]
> columns & 1000 rows....???
> Any ideas would be greatly appreciated...

Reply to this Message

Bernie Deitrick - 02 Apr 2008 14:19 GMT

Joseph,

With the month names in B1:M1, and Job1 etc in A2:Awhatever, enter this into cell B2

=SUMPRODUCT((WORKSHEET1!$A$2:$A$1000=B$1)*(WORKSHEET1!$H$2:$H$1000=$A2)*WORKSHEET1!$E$2:$E$1000)

and copy to match the headers and row labels.

Or use a Pivot table on the original data sheet.... much easier.

HTH,
Bernie
MS Excel MVP

> I'm trying to figure if the following can be streamlined a bit:
>
[quoted text clipped - 5 lines]
> columns & 1000 rows....???
> Any ideas would be greatly appreciated...

Reply to this Message

Rate this thread: