0

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.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
Sarah-Jane
  • 19
  • 1
  • Questions 1) Are your 40 sheet names all in the format "Sheet1" all the way to "Sheet40"? 2) Are the dates in cell C3 text or an Excel date stored as a number and displayed as text. you can test it with `=ISTEXT(C3)` or `=ISNUMBER(C3)` – Forward Ed Apr 20 '16 at 11:37
  • no they are peoples names, and the date is written in the format eg: 23 january 2016 =ISTEXT(C3) is saying false, sorry not to good with spread sheets, I am learning as I go. :) – Sarah-Jane Apr 20 '16 at 11:45
  • I did a little googling and I found an answer for you but the approach I count will require you to index your sheet names in a column somewhere. The better question is are you allowed to use VBA and run some code to get an answer or strictly speaking does it have to be a formula? – Forward Ed Apr 20 '16 at 11:53
  • it has to be a formula – Sarah-Jane Apr 20 '16 at 12:00
  • ARG I have it working for text, I cant get it working for month. – Forward Ed Apr 20 '16 at 12:05
  • What you are looking for is a 3D COUNTIF formula. Its Called 3D because 2D is is rows and columns for dimensions, in this case the third dimension is worksheets. That might give you something to google. – Forward Ed Apr 20 '16 at 12:12
  • whats the text formula because i need that as well, i need to find out how many cells C11 in all the sheets contain the word Completed – Sarah-Jane Apr 20 '16 at 12:14
  • thank you i will google that. :) – Sarah-Jane Apr 20 '16 at 12:14

2 Answers2

1

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))
Forward Ed
  • 9,484
  • 3
  • 22
  • 52
0

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.

JMcD
  • 100
  • 7