Run formula on a specific date range

whiteafro1

New Member
Joined
Feb 13, 2017
Messages
2
I have a workbook with multiple sheets. Each sheet has the dates listed in column A (1/1/17, 1/2/17, 1/3/17 etc). In columns B:K are yes or no answers for each date. I am trying to create a summary sheet that lists the total number of Yes answers for each month and the total number of no answers for the month. I created this formula to run on the summary sheet =SUMPRODUCT(COUNTIF(INDIRECT("'"&L2&"'!$B1:K999"),"yes")) where L2 has the name of my sheet. This will give me the total number of yes answers on each sheet, but I need to only count them if the date in column A is in January for example.

Any help is greatly appreciated.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
Welcome to the board.

Try something like this:

=SUMPRODUCT((TEXT(INDIRECT("'"&L2&"'!A1:A999"),"mmm yyyy")="Jan 2017")*(INDIRECT("'"&L2&"'!B1:K999")="yes"))
 

whiteafro1

New Member
Joined
Feb 13, 2017
Messages
2
Hi Oaktree,

You are a genius. This worked perfectly. Thank you so much for the help!
 

Forum statistics

Threads
1,085,307
Messages
5,382,843
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top