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

[rbrace]

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.

[/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.

=SUMPRODUCT(--(IFERROR(MONTH(PROJECTS!AD2:AD95),0)=12))

=SUMPRODUCT(--(PROJECTS!AD2:AD95<>""),--(MONTH(PROJECTS!AD2:AD95)=4))

=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.

 

[ChrisM]

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]

You should be able to use this formula to count January dates without changing your data:

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

 

[markmzz]

Try this small modification in Barry's formula:

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

Markmzz

 

[barry houdini]

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]

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]

Try this small modification in Barry's formula:

 =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]

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