Sumproduct with row and column criteria

[erniepoe]

Hi All,

I'm trying to do a sum formula along row 17, if the dates in column F are less than the date in C2. Weirdly, this formula results in the incorrect answer, but interestingly enough, if I divide the answer the formula gives by 3, it then results in the correct answer, and this holds for all values along row 17 (J17:N17, O17, P17, etc....)

But before I just divide by 3 and call it a day, I am wondering if somebody would be able to tell me a) why this is happening, and b) if there is an actual formula that delivers the correct answer, rather than having to do this post-formula machination.

Thank you so much for any help!

=SUMPRODUCT((F27:F30<C2)*(J17:M17))

 

[awoohaw]

posting your data could really help. see the xl2bb add in link below.

thanks in advance.

 

[Joe4]

Please show us a small sample, where you show us your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[awoohaw]

Hi All,

I'm trying to do a sum formula along row 17, if the dates in column F are less than the date in C2. Weirdly, this formula results in the incorrect answer, but interestingly enough, if I divide the answer the formula gives by 3, it then results in the correct answer, and this holds for all values along row 17 (J17:N17, O17, P17, etc....)

But before I just divide by 3 and call it a day, I am wondering if somebody would be able to tell me a) why this is happening, and b) if there is an actual formula that delivers the correct answer, rather than having to do this post-formula machination.

Thank you so much for any help!

=SUMPRODUCT((F27:F30<C2)*(J17:M17))

i think your divide by 3 is just a strange coincidence unless there is something inherent in your data that has that property.

 

[Fluff]

Do all cells in F27:F30 have a date?