Formula or Macro? Referencing multiple tabs in formula, but excluding other tabs.

[HNoble]

Hi

I have a spreadsheet where I want to do some reporting against the data. The majority of it is COUNTIF and SUMIF formulas.

The spreadsheet is made up of a few home pages and the rest have the data on them. New tabs can be added and old ones removed so there is not a definite list of tabs to use in the formula. Is there way of calculating the formula against every tab except a few specific ones where the names won't ever change (home pages)?

I wasn't sure if this can be done in a formula or maybe a macro.

Thanks in advance.
Hannah

 

[Joe4]

Take a look here: https://www.extendoffice.com/documents/excel/2541-excel-countif-across-multiple-worksheets.html

The strategy I would probably use is to write the formula that does the calculation across all sheets, then subtract the counts from the specific sheets you want to exclude.
Otherwise, if it is more complex, you might want to consider a VBA solution. If you need help with that, you will need to supply more detailed information (structure of data, sheets to exclude, data examples, etc).

 

[HNoble]

Thanks a lot, that's really helped and seems to be working!

Thanks again

 

[Joe4]

You are welcome.

 

[HNoble]

So the formula works well until I bring another criteria in. It returns a number but it's wrong.

For example, I want to combine these formulas so that it look at both criteria (<100% and >0%) before returning the result:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),"<100%"))

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),">0%"))

$W$4:$W$11 - This lists the tab names to reference.

$I$23:$I$100 - The table of data to look up against in each tab.

I've been using *AND between the two formulas but this is not working.

Any help would be great, many thanks in advance!

 

[Joe4]

You do not use AND between conditions in SUMPRODUCT, you typically use * or a comma.

Try:

[COLOR=#333333]=SUMPRODUCT(COUNTIF(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),"<100%") * [/COLOR][COLOR=#333333]COUNTIF(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),">0%")[/COLOR][COLOR=#333333])[/COLOR]

or

[COLOR=#333333]=SUMPRODUCT(--(COUNTIF(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),"<100%")),--([/COLOR][COLOR=#333333]COUNTIF(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),">0%"))[/COLOR][COLOR=#333333])[/COLOR]

 

[HNoble]

Thank you for that! I've tried it though and it's not bringing back the correct number. It should be bringing back the value 21 but it's bringing back 249 like it's multiplying them. Do you have any other suggestions?

Thanks!

 

[Joe4]

Sorry, I was just going on the presumption that your original formulas were right, and you just needed to combine them.

Maybe try COUNTIFS, which allows for multiple conditions (https://exceljet.net/excel-functions/excel-countifs-function)?
I am not sure if you need SUMPRODUCT around it. Try it with and without, i.e.

[COLOR=#333333]=COUNTIFS(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),"<100%",INDIR[/COLOR][COLOR=#333333]ECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),">0%")[/COLOR]

or

[COLOR=#333333]=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),"<100%",INDIR[/COLOR][COLOR=#333333]ECT("'"&$W$4:$W$11&"'!$I$23:$I$100"),">0%"))[/COLOR]