COUNTIFS - Too Many Arguments

[Cat129]

Hey,

Can someone help me figure out a better formula please?

Below is what I have (I have replaced by sheet names with sheet 1, 2 etc etc...), this works for what I want, but I now need to build on this and when I do I run out of arguments.

I would like to add in that it also needs to look in every single sheet in Column R for "Yes" if Column Q = A2, and total the amount of Yes' across all sheets that match A2

I hope this makes sense to someone and you can help

=SUM(COUNTIFS('sheet 1'!Q:Q,A2),COUNTIF('sheet 2'!Q:Q,A2),COUNTIF('sheet 3'!Q:Q,A2),COUNTIF('sheet 4'!Q:Q,A2),COUNTIF('sheet 5'!Q:Q,A2),COUNTIF('sheet 6'!Q:Q,A2),COUNTIF('sheet 7'!Q:Q,A2),COUNTIF('sheet 8'!Q:Q,A2),COUNTIF('sheet 9'!Q:Q,A2),COUNTIF('sheet 10'!Q:Q,A2),COUNTIF('sheet 11'!Q:Q,A2),COUNTIF('sheet 12'!Q:Q,A2),COUNTIF('sheet 13'!Q:Q,A2),COUNTIF('sheet 14'!Q:Q,A2),COUNTIF('sheet 15'!Q:Q,A2),COUNTIF('sheet 16'!Q:Q,A2),COUNTIF('sheet 17'!Q:Q,A2),COUNTIF('sheet 18'!Q:Q,A2))

Thanks,
Cat

 

[Gerald Higgins]

Maybe replace COUNTIFS with COUNTIF.

 

[Cat129]

There is only one with a countifs that was left behind by accident from when I was trying to do multiple countif's to build it. This isn't the problem, I wish it was that simple.

Thanks anyway

I forgot to add that the answer can be in VBA as this whole spreadsheet is generated from text files, and once I have the formula working I was going to transfer it into VBA.

Thanks

 

[Marcelo Branco]

Create a list containing all the sheet names. Name this range, say, SheetList

And try this formula
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!Q:Q"),A2,INDIRECT("'"&SheetList&"'!R:R"),"Yes"))

M.

 

[RoryA]

I could only see you running out of arguments in the SUM function if you are running 2003 or earlier (or using xls format), in which case you could simply string multiple SUM formulas, or just use + instead. Personally, I'd rethink your data structure - perhaps have a summary table on each sheet so that you can simply sum across sheets.

 

[Cat129]

This looks great thank you, will try it now and let you know how it goes. Thank you

Create a list containing all the sheet names. Name this range, say, SheetList

And try this formula
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!Q:Q"),A2,INDIRECT("'"&SheetList&"'!R:R"),"Yes"))

M.

 

[Cat129]

This works perfectly! You diamond! Thank you so much

Create a list containing all the sheet names. Name this range, say, SheetList

And try this formula
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!Q:Q"),A2,INDIRECT("'"&SheetList&"'!R:R"),"Yes"))

M.

 

[Cat129]

Is there an alternative formula? Although this does exactly what I have asked it has now made the spreadsheet incredibly slow due to the processing required for SUMPRODUCT. If there is no other way then everyone will have to put up with it.

Thanks,
Cat

 

[Marcelo Branco]

Maybe limiting the ranges can improve performance. Something like

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!Q2:Q200"),A2,INDIRECT("'"&SheetList&"'!R2:R200"),"Yes"))

M.

 

[Gerald Higgins]

OR, what about applying the COUNTIF formula to each individual sheet, say in cell A1 on every sheet, and then just make your SUM formula add those up.

Also, do consider RoryA's suggestion of re-thinking your data structure.
If possible, consider having all your data on a single sheet.
This is likely to make lots of other things much easier as well.