SUMPRODUCT Month, December showing totals before anything entered?!

[marko2002]

Hello again all, I have a spreadsheet which calculates my totals on book sales, one of the columns is eBay fee’s and I’m using the following formula on a separate sheet to tell me, month by month, certain things, one of which is the ebay fee’s

=SUMPRODUCT(MONTH(Books.C12:C1200)=12,Books.L12:L1200)

All months return the expected figure until I get to December, which in the example above is the number “12”. Up until this point, all months previous and after December show the expected results, obviously Jan, Feb, Mar, etc show nothing as we haven’t got to those months yet but December (12) is showing a figure which I can’t explain. If I’m right, the above formula basically looks in the column “C” for the “month” then looks in the column “L” for the amount and add’s these amounts up if it finds the month. I do have amounts in “L” after November but no dates entered in column “C” yet for December, so I can’t figure why it’s showing an amount if I don’t yet have December in column “C”.

Can anyone shed any light on this or am I on glue?
Cheers
Mark

[mikekaganski]

A date is just a number, that is handled as number of days since “day zero”, which is Dec 30th, 1899.

Any empty cell would be treated as containing zero. So it would give MONTH(emptycell)=MONTH(0)=MONTH(1899-12-30)=12 to be true.

You may also want to check for IsBlank

[marko2002]

I can see where you’re coming from but the confusing thing is all the other months are acting as I would have expected, both before December and after. If I place a date, say January into column “C” then the total for January rises by the amount it finds in column “L” which is exactly as it should be. I can take this date away and the total is reduced accordingly. I started the spreadsheet in August and so far have the correct totals for Aug, Sep, Oct and Nov - nothing showing for Jan, Feb, Mar, etc as I haven’t entered any of these dates in column “C” yet, but the December total has basically ignored that it needs to see the “12th” month in “C” before it add’s anything - I understand your concept of empty cell but I can’t figure out why the month “12” is a problem, yet every other month isn’t?.

Is it possible to give an example of how I would integrate “IsBlank” into the formula to exclude empty cells as I’ve tried myself and pretty much made a complete hash of it !
Thanks
Mark

[mikekaganski]

but the confusing thing is all the other months are acting as I would have expected
I understand your concept of empty cell but I can’t figure out why the month “12” is a problem, yet every other month isn’t

Clearly you don’t. Simply put, all your empty cells are actually cells with a date “Dec 30, 1899”. No other strange dates there, so nowhere to get problems for other months.

=SUMPRODUCT(NOT(ISBLANK(Books.C12:C1200)); MONTH(Books.C12:C1200)=12; Books.L12:L1200)

[marko2002]

Thanks for that Mike, I thought the date you gave earlier was merely an example date, I didn’t realise the formula would literally take the empty cell as that specific date which now makes me realise the significance of December lol - Regards, Mark