Sumproduct with other sheets and criteria?

**aqalna** · 10-11-2016, 04:11 PM

Hello Everyone,

was hoping anyone can help me out in this formula that I am trying to create.

So my current formula was =(SUMPRODUCT((Sheet2!$F$3:$Q$3<=$E$2)*(Sheet2!F7:Q7))) and that formula was great when everything was static ...however since my work is now dynamic I need to fine tune my formula..I added my excel sheet and in the first example it shows when it is static...however now in the second example ...it is dynamic

So two issues I keep getting into:

1) it populates as "Value" ...so I dont know why it is causing that issue

2) How do I develop the criteria when the month M5 is chosen than it needs to capture all the data that is less and equal to M5 (so M1, M2, M3,M4, and M5) and including making sure it captures the correct Area's ie: A1, A2, etc...

so in essence in Example 2 I would want the total to be $6 ,153.

Let me know if anyone knows how to help me in this situation please

**protonLeah** · 10-11-2016, 07:00 PM

G28:

Please Login or Register  to view this content.

**aqalna** · 10-12-2016, 12:22 PM

This formula works for having selected each M1, M2 M3, but I am trying to create it where it adds everything up from the month selected

so ie: Chosen M5 than it should have (M1 + M2 + M3 + M4 + M5)

**protonLeah** · 10-12-2016, 08:40 PM

Originally Posted by aqalna

...Chose M5 then it should have (M1 + M2 + M3 + M4 + M5)

o.k. this formula assumes M1 as the first column and you choose the last column:

Please Login or Register  to view this content.

However, this formula lets you choose both the first M# and the last:

Please Login or Register  to view this content.

But note that they generate a single column of sums, not the table you had previously.

**aqalna** · 10-12-2016, 11:42 PM

Amazing formula man...your a genius!

quick question though...if i added a total for m1 to m6 (attached the file) but I don't want the total to be included when you select for ie: m7 so basically i only want m1 to m7 and not double count the total part...which inflates the total amount from m1 to m7

**aqalna** · 10-12-2016, 11:47 PM

so in the file it is adding the total amount if you choose m7 to m12 which I don't want it to be included...is there a way to exclude it? I may need to exclude other total so for example i added another total too and attached a new version of the excel file.

**protonLeah** · 10-14-2016, 12:06 AM

Please Login or Register  to view this content.

**aqalna** · 10-17-2016, 04:09 PM

-IF(AND(MATCH($G$26,Sheet2_M_Rows,0)>3,MATCH($G$26,Sheet2_M_Rows,0)<7),INDEX(Sheet2!$J$6:$J$17,MATCH(F27,Sheet2AreaList,0)),IF(MATCH($G$26,Sheet2_M_Rows,0)>6,SUM(INDEX(Sheet2!$J$6:$J$17,MATCH(F27,Sheet2AreaList,0)),INDEX(Sheet2!$N$6:$N$17,MATCH(F27,Sheet2AreaList,0))),0))

In this part of the formula "IF(AND(MATCH($G$26,Sheet2_M_Rows,0)>3,MATCH($G$26,Sheet2_M_Rows,0)<7)"

...I just need further help in it please to understand the logic and learn it so I can help later on other people too

I am trying to track where does this part symbolize exactly?

what are those two parts saying if anything is less than >3 in column's wise? I am just trying to understand how will the formula read this by the titles I put in like m3 and m7 or is it reading the column's and saying the column with data that...because then the other part of your formula you have IF(MATCH($G$26,Sheet2_M_Rows,0)>6

so it lost me there :S

**aqalna** · 10-17-2016, 04:41 PM

I guess I am trying to see how I can apply this formula in other methods like replace m1 to m3 Jan-16 to Dec-16 and the totals are now 2016, 2017, 2018 etc....

**aqalna** · 10-17-2016, 04:50 PM

I ended up playing with the formulas and found it to work with this

=SUM(INDEX(Sheet2!$G$6:$G$17,MATCH($F27,Sheet2AreaList,0)):INDEX(Sheet2!$G$6:$AL$17,MATCH($F27,Sheet2AreaList,0),MATCH($G$26,Sheet2_M_Rows,0)))-IF(AND(MATCH($G$26,Sheet2_M_Rows,0)>12,MATCH($G$26,Sheet2_M_Rows,0)<27),INDEX(Sheet2!$S$6:$S$17,MATCH(F27,Sheet2AreaList,0)),IF(MATCH($G$26,Sheet2_M_Rows,0)>26,SUM(INDEX(Sheet2!$S$6:$S$17,MATCH(F27,Sheet2AreaList,0)),INDEX(Sheet2!$AF$6:$AF$17,MATCH(F27,Sheet2AreaList,0))),0))

So from my understanding than ...if it is safe to assume that the ">3" and ">7" and >6" is calculating the columns correct?

**protonLeah** · 10-17-2016, 05:47 PM

In the V3 workbook, you have TOTAL (m1 to m3) between M3 and M4, so if you choose M4, M5 or M6, that total will be included in the SUM calculated by the first part of the formula. The

Please Login or Register  to view this content.

part of the formula checks if the selected M# is M4- M6 and subtracts out TOTAL (m1 to m3). If you choose M7 - M12 the first part of the formula calculates a sum that includes both TOTAL (m1 to m3) and TOTAL (m1 to m6), so the second part

Please Login or Register  to view this content.

checks if M > 6, if so it adds the two totals

Please Login or Register  to view this content.

and subtracts them so that they are not double added.

**aqalna** · 10-17-2016, 07:38 PM

Hmm...I see thanks for the explanation...I will reflect and try to get my head wrapped around it....so the formulas that I created will it work with my new category headings(ie: Jan-16 to Dec-16 to 2016 to Jan-17 to Dec-17 to 2017 etc...?

let me know and thanks so much!

another note: The formula that you help me on

=SUM(INDEX(Sheet2!$G$6:$G$17,MATCH($F27,Sheet2AreaList,0)):INDEX(Sheet2!$G$6:$AL$17,MATCH($F27,Sheet2AreaList,0),MATCH($G$26,Sheet2_M_Rows,0)))-IF(AND(MATCH($G$26,Sheet2_M_Rows,0)>12,MATCH($G$26,Sheet2_M_Rows,0)<27),INDEX(Sheet2!$S$6:$S$17,MATCH(F27,Sheet2AreaList,0)),IF(MATCH($G$26,Sheet2_M_Rows,0)>26,SUM(INDEX(Sheet2!$S$6:$S$17,MATCH(F27,Sheet2AreaList,0)),INDEX(Sheet2!$AF$6:$AF$17,MATCH(F27,Sheet2AreaList,0))),0))+SUM(INDEX(Sheet3!$G$6:$G$17,MATCH($F27,Sheet3AreaList,0)):INDEX(Sheet3!$G$6:$AL$17,MATCH($F27,sheet3AreaList,0),MATCH($G$26,sheet3_M_Rows,0)))-IF(AND(MATCH($G$26,sheet3_M_Rows,0)>12,MATCH($G$26,sheet3_M_Rows,0)<27),INDEX(Sheet3!$S$6:$S$17,MATCH(F27,sheet3AreaList,0)),IF(MATCH($G$26,sheet3_M_Rows,0)>26,SUM(INDEX(Sheet3!$S$6:$S$17,MATCH(F27,sheet3AreaList,0)),INDEX(Sheet3!$AF$6:$AF$17,MATCH(F27,sheet3AreaList,0))),0))

So basically, can I re-copy the formula to apply it to multiple sheets with the same criteria as long as I added and change the "sheet2" to "sheet3"

Sumproduct with other sheets and criteria?

LinkBack

Thread Tools

Rate This Thread

Display

Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Re: Sumproduct with other sheets and criteria?

Thread Information

Users Browsing this Thread

Similar Threads

Use SUMIF or SUMPRODUCT across multiple sheets using a single cell to match criteria

SUMPRODUCT for multiple Criteria across Columns and Rows as well as Sheets

[SOLVED] SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple sheets.

[SOLVED] Sumproduct with two criteria over multiple sheets - formula error

SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

[SOLVED] Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

sumproduct two criteria across 12 sheets

Bookmarks

Bookmarks

Posting Permissions