Forum Discussion
sumproduct returns incorrect value
Novice user here. I am looking for properly calculating formula to search in sheet "Rental #1" column D for any and every entry for month January and return a sum value to current cell in another sheet multiplied by 100. Currently using this formula below. The problem with the current formula - when calculating the results for January it calculates every line in column D even though there is no entries (column D in sheet "Rental #1" is blank). It calculates for every other month properly. Only gives incorrect result for month of January. Any suggestion greatly appreciated.
=SUMPRODUCT(--((MONTH('Rental #1'!$D$7:$D$145)=1)))*100
2 Replies
Harun24HR Thank you for your reply. I think I did not explain correctly what I need. A user will be inputting data in "Rental #1" sheet in Column C - when guest checking in, in Column E - how many nights guest stayed. Column D automatically calculates check out date. In example below there were 5 check out dates in January and 3 in February. I need in "Variable Expenses" sheet in Column E - formula automatically calculate how many checkout dates were in January (in this example 5) and multiply it by 100. As can be seen in the example below the formula I am currently using is working properly for February (formula also work properly for every other months). But it does not work properly for January and return a value of 14000 instead of 500. Hope it is more clear now 🙂
