Issue with Sumproduct and Date Range Criteria

[Aimee S.]

Hello,

Below is a formula I want to use where column BM = project completion date, cells V4 and Y4 are 1/1/16 and 12/31/16, respectively, column C is checking the program type (text value) in cell B98, and the total budget $s I want to conditionally sum are column W.

I want to sum only the total budget from column W if the project completion date in column BM falls within 2016 using cell reference dates that can easily be updated in 2017 and moving forward by entering the first day and last day of the year I desire to review.

The below formula returns nothing, no error, just nothing, and I think it has to do with the date parameters, but could certainly use some assistance if anyone is willing! Thanks in advance for your time!

=SUMPRODUCT(--('BI Report Data-Only Drop'!$BM$2:$BM$15000>=$V$4),--('BI Report Data-Only Drop'!$BM$2:$BM$15000<=$Y$4),--('BI Report Data-Only Drop'!$C$2:$C$15000=$B98),--('BI Report Data-Only Drop'!$B$2:$B$15000="Not Active"),'BI Report Data-Only Drop'!$W$2:$W$15000)

 

[Jonmo1]

Either V4 and/or Y4 are not real dates
OR
The dates in BM2:BM15000 are not real dates.

Test with isnumber
=ISNUMBER(V4)
=ISNUMBER(Y4)
=ISNUMBER('BI Report Data-Only Drop'!$BM2) <- filled down to end of data

Which one(s) are FALSE ?

 

[Aimee S.]

Good thinking. V4 and Y4 are definitely dates, producing a TRUE value with ISNUMBER. However, the source column against which those dates are checking returned a FALSE with ISNUMBER even though they appear as dates (e.g. 2/14/16). That is even more odd because when I right click on the source column and check cell format, it shows Date format mm/dd/yy. O____O What the heck??

Could it be because the BM Column source dates are the result of a logical IF test formula pulling from another column? Would that affect efficacy?

Either V4 and/or Y4 are not real dates
OR
The dates in BM2:BM15000 are not real dates.

Test with isnumber
=ISNUMBER(V4)
=ISNUMBER(Y4)
=ISNUMBER('BI Report Data-Only Drop'!$BM2) <- filled down to end of data

Which one(s) are FALSE ?

 

[Aimee S.]

Hey Jonmo1.

So I fixed part of the issue by inserting a column that pulls the dates in column BM and multiplies that date by 1, creating a definite number-formatted value. There has to be something wonky on the BI/BO Reporting side of things that produces the raw data/report with the date columns formatted in the strange way that they are....

So I took care of that side of things, however, it would appear the last component of the formula which entails checking source column C for a text value of "Cable - Bend" located in cell B98 isn't working (I verified this was an issue by removing that portion of the SUMPRODUCT formula, and the formula pulled in a $ amount, but went blank when that component was re-added back in).

Once again, here is the formula with updated references. Cell B98 contains the text "Cable - Bend" and column C:C on the other sheet is where this value appears. Am I not handling that portion of the formula correctly?

=SUMPRODUCT(--('BI Report Data-Only Drop'!$A$2:$A$15000>=$V$4),--('BI Report Data-Only Drop'!$A$2:$A$15000<=$Y$4),--('BI Report Data-Only Drop'!$C$2:$C$15000=$B98),--('BI Report Data-Only Drop'!$C$2:$C$15000="Not Active"),'BI Report Data-Only Drop'!$X$2:$X$15000)

Either V4 and/or Y4 are not real dates
OR
The dates in BM2:BM15000 are not real dates.

Test with isnumber
=ISNUMBER(V4)
=ISNUMBER(Y4)
=ISNUMBER('BI Report Data-Only Drop'!$BM2) <- filled down to end of data

Which one(s) are FALSE ?

 

[FDibbins]

check for leading/trailing spaces in your data. Find what you think is a match, and then compare your criteria with your "find"...=exact(criteria-cell, find-cell) If you get FALSE, they are not the same, you will need to find where they differ

which version of excel are you using?
Have you considered using SUMIFS()?

 

[Aimee S.]

Thanks FDibbins. I decided to go with SUMIFS as you suggested and it worked great. Appreciate both your help as both your suggestions helped make it happen.

Kind Regards!!!

Aimee

check for leading/trailing spaces in your data. Find what you think is a match, and then compare your criteria with your "find"...=exact(criteria-cell, find-cell) If you get FALSE, they are not the same, you will need to find where they differ

which version of excel are you using?
Have you considered using SUMIFS()?

 

[FDibbins]

Glad it worked for you, thanks for the feedback