Trying to streamline formula

[Joseph]

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

 

[JP]

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

 

[Bernie Deitrick]

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