Sum Product

[Veronica_sky]

SUMPRODUCT((RIGHT(BQ!$B$4:$B$2082,2)="PL")*BQ!$H$4:$H$2082)

This is the formula that i manage to create if everything is in same worksheet for a particular product.

Here is my problem: Is that possible i wan to use sumproduct formula to find the product in several worksheets (as all the format is the same for all the worksheets). I have tried this formula
SUMPRODUCT((RIGHT(Site Clearance & Earthwork:BWICS!A:A,2)="PL")*Site Clearance & Earthwork:BWICS!G:G)

but faild

 

[jeffreybrown]

Hi Veronica_sky,

How about...

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A:A"),"*PL",INDIRECT("'"&SheetList&"'!G:G")))

SheetList is a named range.

Place the names of your worksheets somewhere on your sheet, highlight the range, then in the Name Box (above column A) type in SheetList and hit enter.

 

[Armando Montes]

sumproduct does not accept entire columns, try a1:a65000 for instance

 

[jeffreybrown]

I believe that is correct in versions below 2007, but in 2007 is okay.

 

[Veronica_sky]

Hi jeffreybrown,

"Place the names of your worksheets somewhere on your sheet, highlight the range, then in the Name Box (above column A) type in SheetList and hit enter."

The above is it meant that i have to key in all the worksheet's name in the worksheet i use that formula?

 

[jeffreybrown]

No, not all sheets just one.

Let's say you are using this formula on a sheet called Summary, range A1.

In Maybe D1 I would type in the name of the first sheet with D2 containing the next sheet and so on.

Then hightlight D1:D2, or however long, and name this range SheetList

 

[Veronica_sky]

jeffreybrown:
I got it. Really thanks a lot and appreciate it very much