Counting date based on month excluding hyphen

0
votes

I have number of tasks assigned to me in Excel sheet (Sheet name: Status) where i enter closed date in column AC. I want to count closed tasks based on month and year of closing. But i have many tasks not closed and i enter in the corresponding cell(in column AC) with hyphen(-). I use the following formula to count the number of task closed.

SUMPRODUCT((MONTH(Status!$AC$1:$AC$100)=MONTH(DATEVALUE(G21&" 1")))*(YEAR(Status!$AC$1:$ACA$100)=2020))

If cells from AC1 to AC100 are entered with date, the formula works. But when i have task not closed i enter hyphen(-) in some cells(between AC1 to AC100) the formula returns the error #VALUE!

Can you please help how to count closed tasks based on month and year in the range AC1:AC100 excluding hyphens with a formula in a cell(H21). I have month name(format: MMM) in another sheet starts from G21. Formula entered in H21.

1
exceldateexcel-formulacount
Please post an image to make it more clear what you have and what you want. – Excel Hero
Should YEAR(Status!$AC$1:$ACA$100)=2020 actually be YEAR(Status!$AC$1:$AC$100)=2020 i.e. columns AC NOT ACA? – John F
@JohnF: Sorry that was a typing error. Range is AC1:AC100 – Karthikeyan
@ExcelHero: sorry i couldn't image in the comment. I don't know how to add image – Karthikeyan

1 Answers

0
votes

The #VALUE error occurs because the MONTH and YEAR functions expect a serial date as the argument. By using a -, which is text, the MONTH and YEAR functions return a #VALUE error, which causes the rest of the formula to return #VALUE.

You could use a blank cell instead of "-" for open tasks, and your formula will work. If you MUST use "-", then first modify the "-" using, for example, an array IF:

=SUMPRODUCT((IF($AC$1:$AC$100="-",0,MONTH($AC$1:$AC$100))=MONTH(DATEVALUE(G21&" 1")))*(IF($AC$1:$AC$100="-",0,YEAR($AC$1:$AC$100))=2020))

EDIT Ihave added an image of the formula working below: enter image description here