SUMPRODUCT help: Extract data from different worksheets within the same workbook

[Lifeseeker]

Hi there,

I have a summary table collecting data from three worksheets for reporting purposes. Please find the attached.

With the help from you guys, I am able to use the sumproduct formula to extract data. The current challenge I have is using the same formula but to extract data from different worksheets.

In column P of the summary table in worksheet " a data entry", this essentially is another condition that needs to be added to the sumproduct, but I'm not sure how to go about doing that.......so it's breaking it down further by different sites. (total 3 sites)

Also note the use of self-defined range values such as "RangA and "RangB", which is only applied from worksheet "A data entry"....

Any help is much appreciated.

Thank you

[brokenbiscuits]

To break down by site, you would use a syntax such as:

=SUMPRODUCT(($C2:$C11=$K2)*($D2:$D11="yes")*($E2:$E11=$P2))

But using your named ranges instead of the C2:C11, etc.

[Lifeseeker]

Thank you. Helpful.

In my previous post I had mentioned that three worksheets would be used for data entry while a summary table would be used to collect all of those data and put them in one place.

I am not sure how the syntax should go in terms of being able to pull data from different worksheets.

In the attached file, you will see 4 worksheets, and the "overall reporting" sheet is the summary table that pulls data from all the other 3 sheets using SUMPRODUCT, but i'm not sure how the formula should be written. RangA and RangB only apply to "A Data Entry" worksheet, but wouldn't they have to apply to the others as well?

Any help is much appreciated.

[Lifeseeker]

Hi,

I'm somehow able to combine two big sumproduct formula into one, but once I hit enter, I get prompt window asking to update the parameter values. If I cancel out of it, values all turn into "VALUE! in cells.

Does anybody else get the same thing? Please see the attached.

Formula in 'overall reporting' worksheet, E3 is

Please Login or Register  to view this content.

If you get rid of the second SUMPRODUCT, it all works out perfectly. But I need at least another SUMPRODUCT in there for the other worksheet.

Thank you