CODEWITHSUNDEEP
excelexcel-formulacountifformulas
Sarah-Jane
Sarah-Jane

Reputation: 19

Countif linked to all sheets in the workbook

Hello I am trying to use countif to add up how many cells have January in. For example I have 40 worksheets and I need to find out how many of them contain january in the date in cell C3 on all of the sheets. and put the answer in another sheet.

Upvotes: 0

Views: 142

Answers (2)

JMcD
JMcD

Reputation: 100

You can use a 3-D formula, but unfortunately, the only formulas that allow 3-D references are mathematical functions that don't have much in the way of logic (no IFs, ANDs, etc.). If you restructure your workbook a little bit to, say, add IF(C3="January",1,0) in Z3 on each sheet, then you can do SUM(Sheet2:Sheet40!Z3) on your summary sheet.

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

OPTION 1) Formula Method

1) Write out a list of all the names of your sheets in a column.
2) On the "Formulas" ribbon, select Define Name.
3) Give it a name in the top box, ie. SHEETNAMES

Then use this formula:

=SUMPRODUCT(COUNTIF((INDIRECT("'"&SHEETNAMES&"'!C11")),"Completed"))

I would have thought the formula for your original question would have been very similar but it keeps showing up as an error. I have been using the following:

=SUMPRODUCT(--(MONTH(INDIRECT("'"&SHEETNAMES&"'!C3"))=1))

Upvotes: 1

Related Questions