Modify SUM formula to include MATCH condition

Ian,
The function you are looking for is SUMIF if looking for a single criteria or SUMIFS if you have multiple criteria.

Hi Joe thanks for the quick response. I have used the SUM formula as shown in the sheet for some time and had no issues.

Hi Ian,

You will need a different setup to sum the cells across the sheets with a criteria.

In the attached, I listed all the sheets required to sum the cells in column P in the range P2:P4 and created a Named Range called "MySheets".

And then you can use the following formulas to sum the cells across the sheets....

Prof/Loss:

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!"&"F1:I1"),A7,INDIRECT("'"&MySheets&"'!"&"F12:I12")))

Select allOpen in new window

Selections

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!"&"F1:I1"),A7,INDIRECT("'"&MySheets&"'!"&"F15:I15")))

Select allOpen in new window

Please refer to attached for more details.
matchdata.xlsx

Hi Neeraj, this looks impressive. I will be using one sheet per day for a month so need up to 31 sheets.
So how can I match the names ? There is no match condition in your formula.
On the Program sheet I need to sort columns daily and important to match names between the Program sheet and the 30+ sheets

Right now the named range MySheets contains only three sheets listed in the range P2:P4.
If you need to sum across 31 sheets, list them all say in column P and edit the named range MySheets to include all the cells with the sheet names.

Please refer to the attached.
matchdata-v2.xlsx

Looking good, still applying to my sheets. Need an hour or two and will come back to you at which time it will be breakfast time your end :)

No problem.
It is 1:20 AM here so going to log out now. :)

You placed the mysheets into column P I want to change it to col AG How do I do that without #REF errors ?
Thanks

DO NOT SELECT THIS COMMENT FOR POINTS

Go to Formulas > Name Manager and edit the Mysheets to the new range.

For that you will need to create the Sheet Name list in column AG similar to one which is in column P in the file I uploaded.

Now you can edit the Old Named Range so that now it would point to your new range in column AG instead of column P.

All the named ranges appear in the Name Manager. Name Manager is on Formulas Tab.


Now follow the instructions in the following image to edit the Named Range.

Also remember that if a sheet doesn't exists in the workbook but it's name is included in the list of sheet names in column AG, your formula will return #REF! error.

So make sure to list only those sheets which are available in the workbook. Also make sure that the sheet names listed in column AG and the name of the available Sheet Tabs are exactly same including leading or trailing spaces if any.

Good Morning Neeraj,
I have created the name range in G6:G36 However I now get a  #NAME? error

Good morning Ian,

#NAME? error simply means the formula you placed in the formula cell is an invalid formula.

Just type the formula correctly and see where it uses a single quote and double quotes in the formula carefully.

Look at the formula I posted within the code tags, there you can differentiate between a single quote and double quote.

Here is the formula, all the 31 sheets have P1:AP1 & P12:AP12
The Named range is Program G6:G36
{=IF(A7="","",SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!"&"P1:AP1"),A7,INDIRECT("'"&MySheets&"'!"&"P12:AP12"))))}

Your formula seems correct.
Anyways, I have changed the formula and named range as per your new setup in the attached.
matchdata-v3.xlsx

Still not working on my sheet
Why do you have P11 in the formula should be P1

Works very well Neeraj. I can see why you are a genius.
Thanks
Ian

You're welcome Ian!
Thanks for the feedback and kind words. :)

Regards,
Neeraj