Counting data across multiple worksheets

Question

I am trying to create a workbook to track vacation time for employees. I have 12 worksheets ( Jan, Feb, Mar etc ). Starting at B1 of every sheet, I have the dates of the month increasing from left to right.

NAME	1/1/2019	1/2/2019	1/3/2019	1/4/2019	1/5/2019
	V  or 8

My last worksheet is a page for totals

NAME	VACATION ALLOWED	VACATION SCHEDULED

What I am trying to do is create a workbook that anytime a vacation day is entered on any of the worksheet pages, it will update to the last page under "Vacation Scheduled". I can use " =COUNTIF(JAN!B2:AF2,D1) " and it will work for one page, but when I try and span worksheets I get #VALUE!.

I would like it to count vacation hours scheduled, but would settle for counting vacation days scheduled. Thanks for your time. Windows 7 Pro, Excel Version 14.0.7, Office 2010

Answer 1

Hi,

Try this

=SUMPRODUCT(COUNTIF(INDIRECT($M$4:$M$15&"!B2:AF2"),D1))

M4:M15 has the tab names i.e. Jan, Feb, Mar etc.  Ensure tha the spellings match.

Hope this helps.