SUMPRODUCT(SUMIF(INDIRECT(........Help

Long Nose

Long Nose

Board Regular
Joined
Nov 19, 2007
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Originally Posted by Aladin Akyurek View Post
You could create on every relevant sheet an additional column, say, G, with"

=MONTH(A2)

Then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!G2:G188"),MONTH($A6),INDIRECT("'"&SheetList&"'!F2:F188")))

where SheetList refers to a range housing the relevant sheet names, that is, 3107, 3207, etc.
Click to expand...

I have this great formula that Aladin created and I'd like to add one more criteria to the MONTH($A6).

Here's my current formula

=SUMPRODUCT(--(SUMIF(INDIRECT("'"&Sheetlist_New&"'!A9:A200"),ED3,INDIRECT("'"&Sheetlist_New&"'!E9:E200"))),--(SUMIF(INDIRECT("'"&Sheetlist_New&"'!AB9:AB200"),MONTH(EL5),INDIRECT("'"&Sheetlist_New&"'!E9:E200"))))

The result here isn't adding an additional criteria, but taking the second criteria times the first.

So, I'm trying to now add a second criteria before E9:E200, but the formula is erroring out.

=SUMPRODUCT(SUMIF(--(INDIRECT("'"&Sheetlist_New&"'!A9:A200",ED3),--(INDIRECT("'"&Sheetlist_New&"'!AB9:AB200",MONTH(EK5))),INDIRECT("'"&Sheetlist_New&"'!E9:E200"))))

Can anyone see my error?
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Somewhat unclear... Can you explain in words what it is you'd like to do?
 
Upvote 0
and I'd like to add one more criteria
Click to expand...

What version of Excel do you have?

If 2007:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist_New&"'!E9:E200"),INDIRECT("'"&Sheetlist_New&"'!A9:A200"),ED3,INDIRECT("'"&Sheetlist_New&"'!AB9:AB200"),MONTH(EK5)))

will sum the E9:E200 in all the sheets when both A9:A200 = ED3 and AB9:AB200 = MONTH(K5).

If you have an earlier version, I'm afraid I'll have to defer to the gurus for multiple criteria. :)
 
Last edited:
Upvote 0
AdamL said:
What version of Excel do you have?

If 2007:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheetlist_New&"'!E9:E200"),INDIRECT("'"&Sheetlist_New&"'!A9:A200"),ED3,INDIRECT("'"&Sheetlist_New&"'!AB9:AB200"),MONTH(EK5)))

will sum the E9:E200 in all the sheets when both A9:A200 = ED3 and AB9:AB200 = MONTH(K5).

If you have an earlier version, I'm afraid I'll have to defer to the gurus for multiple criteria. :)
Click to expand...

I'm using 2003.

Sorry I'm not being clear. In the original formula (see Aladin) we are summing based on one criteria MONTH($A6). I'd just like to add a second criteria using the same indirect functions. I haven't tried your solution yet. I'll see if 2003 can handle it.

Thanks!
 
Upvote 0
Download and install the free add-in, Morefunc.xll, and use the THREED function. Otherwise, without the add-in, you can use the following complex and inefficient formula...

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&SheetList&"'!A9:A200"),ROW(INDIRECT("9:200"))-9,0,1))=ED3),--(N(OFFSET(INDIRECT("'"&SheetList&"'!AB9:AB200"),ROW(INDIRECT("9:200"))-9,0,1))=MONTH(EK5)),N(OFFSET(INDIRECT("'"&SheetList&"'!E9:E200"),ROW(INDIRECT("9:200"))-9,0,1)))

Note the following:

1) If A9:A200 contains numerical values, change the T (in red) to N.

2) SheetList needs to refer to a horizontal range of cells, not vertical.

Hope this helps!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Sumproduct sumif indirect
Replies
5
Views
650
awoohaw
awoohaw
N
  • Solved
Summarizing data in a table using Sumproduct Sumif Indirect with named ranges and table references
Replies
3
Views
461
Fluff
Fluff
S
  • Question Question
Help with conditional SUMPRODUCT
Replies
2
Views
760
Sponny
S
F
  • Question Question
HOW TO USE SUMPRODUCT, SUMIFS AND INDIRECT TOGETHER
Replies
2
Views
2K
FATI
F
K
  • Question Question
SUMPRODUCT(SUMIF(INDIRECT Formula not working
2 3
Replies
29
Views
2K
KRice
K

Forum statistics

Threads
1,222,580
Messages
6,166,880
Members
452,080
Latest member
Akin Himself

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top