Counting January dates with sumproduct from cells populated with if statement “” evaluates #value!

rbrace

New Member
Joined
Apr 30, 2012
Messages
14
Excel 2007.

I have a workbook with three main sections that share information.


1. Project sheets. These have random sheet names and contain project information. In cell S16 I am calculating a start date based on project type and status. I am checking to see if cell 108 is filled in to indicate the project is active and trying to keep the cell free of 1/1/1900 if project is not active.
Code:
[/FONT][/COLOR][COLOR=black][FONT="Calibri"] [/FONT][/COLOR][COLOR=blue][FONT="Arial"]=IF(S108<>"",(IF(OR(B4="REM",B4="EXP"),(S108+0+CHOOSE(WEEKDAY(S108+0),1,0,0,0,0,0,2)),(S108-55+CHOOSE(WEEKDAY(S108-55),1,0,0,0,0,0,2))))+(IF(S108="Express",35,0)),"")

2. A index page called PROJECTS uses VB to pull information from the project sheets above into a print friendly sheet. The information from #1 above ends up in row AD.

3. A report page is where I want to count all the projects by month from PROJECTS!AD2:AD95. I have used the following formulas but they all return #value!. The error is from cells of projects that have not started and the if statement from #1 above. If I click these cells and press delete (even though they are blank already) the formula will work.

I have tried the following to count January dates from PROJECTS!AD2:AD95.

Code:
=SUMPRODUCT(--(IFERROR(MONTH(PROJECTS!AD2:AD95),0)=12))
Code:
=SUMPRODUCT(--(PROJECTS!AD2:AD95<>""),--(MONTH(PROJECTS!AD2:AD95)=4))
Code:
=SUMPRODUCT((MONTH(PROJECTS! AD2:AD95)=1)*(PROJECTS!AD2:AD95<>""))


I would like to skip over the #value! errors or prevent them from occurring.
Help would be appreciated as I am teaching myself formulas.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
I think I would set item #1 to return a zero instead of "". Then use conditional formatting rules to set font = white on values equal to zero, and then you won't see the 1/1/1900.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You should be able to use this formula to count January dates without changing your data:

=SUMPRODUCT(--(TEXT(PROJECTS!AD2:AD95,"mmm")="Jan"))
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Try this small modification in Barry's formula:

Code:
=SUMPRODUCT(-(PROJECTS!AD2:AD95<>""),-(TEXT(PROJECTS!AD2:AD95,"m")="1"))

Markmzz
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Hello Markmzz,

If I understand correctly you don't need to check that the cells are not blank - those cells are not truly blank but populated with "", hence they won't return "Jan" or "1" but simply "", so I think the TEXT function alone should be sufficient, i.e.

=SUMPRODUCT(--(TEXT(PROJECTS!AD2:AD95,"m")="1"))

It's those same "" values which are giving #VALUE error when MONTH function is applied
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Hello Markmzz,

If I understand correctly you don't need to check that the cells are not blank - those cells are not truly blank but populated with "", hence they won't return "Jan" or "1" but simply "", so I think the TEXT function alone should be sufficient, i.e.

=SUMPRODUCT(--(TEXT(PROJECTS!AD2:AD95,"m")="1"))

It's those same "" values which are giving #VALUE error when MONTH function is applied

Hi Barry,

If the range PROJECTS!AD2:AD95 don't have blank cell, you are right (not 1/1/1900).

Maybe I'm wrong, but I think that it have.

Let's wait for the user.

Markmzz
 

rbrace

New Member
Joined
Apr 30, 2012
Messages
14
Try this small modification in Barry's formula:

Code:
 =SUMPRODUCT(-(PROJECTS!AD2:AD95<>""),-(TEXT(PROJECTS!AD2:AD95,"m")="1"))

Markmzz


Thank you all for the suggestions and corrections. I tried all the options above and markmzz version worked in my case.

Thank you all again.
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Thank you all for the suggestions and corrections. I tried all the options above and markmzz version worked in my case.

Thank you all again.

You are welcome and I'm glad to help.

Markmzz
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,884
Members
414,413
Latest member
Sinbin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top