HOW TO COMBINE SUMIF & INDIRECT FORMULA...sheets may or may not exist

[JuicyMusic]

Guru's, I woke up this morning and said I am going to combine SUMIF and INDIRECT by myself. Well, after floating around the internet and watching YT videos........not one formula worked. I get the NAME and REF error.

There is a Summary tab: Column A4:A34 = Date by day vertically, example 1/1/2023 and on.
Column B = sheet names of all the other tabs to the right of the Summary tab.....that may or may not be created after I run a VBA that creates these tabs IF their names are in this Column B.


How do you write a SUMIF formula on the Summary tab.......(with one criteria)........that is looking to sum data from the Column C of 30 or 31 sheets that may or may not exist?
Right now I have to link each day to each new sheet......28 times

Here is my SUMIF formula from the Summary tab in cell C4:

=SUMIF('HGI jan 1'!$A$2:$A$416,'HGI -Tax Recon'!C$3,'HGI jan 1'!$C$2:$C$416)

The cream-colored column is the column that a code I'm using looks at to create additional sheets from. If there are only 28 days in a month, then the code will create only 28 days.

Thank you and thank you in advance!!!!!

 

[James006]

Hi,

Say, your sheet name is located in cell A1 : HGI jan 1

Then your formula could be

=SUMIF(INDIRECT("'"&A1&"'!"&"$A$2:$A$416"),'HGI -Tax Recon'!C$3,INDIRECT("'"&A1&"'!"&"$C$2:$C$416"))

Hope this will help

 

[JuicyMusic]

Hi James, I tried it and I get a REF error. My SUMIF is summing line items from another sheet. Does this INDIRECT point to the other sheet....which is the 2nd snapshot?
Thank you so much.

 

[James006]

No sure to understand your remark ...

As explained above, if in cell A1 you store a sheet name, indirect("A1") will direct you to this sheet ...

 

[JuicyMusic]

James006, please accept my aplogies for coming back late. It worked and TYSM!

 

[James006]

Glad you managed to fix your problem

 

[JuicyMusic]

You guys are awesome!