SUMPRODUCT help please

[BamLindsay]

Hello,

Hoping someone can help me with a sumproduct formula that is not giving me the right answer please. I have a P&L with month in the column and GL code in the row and I am trying to create a formula that will add all the columns less than or equal to a given date input so that I can calculate the year to date number depending on month input.

The formula seems to work on most of the rows but not for all of them (see above). The formula I am using is =SUMPRODUCT(($AF$14:$MX$179)*($B$14:$B$179=$B45)*($AF$7:$MX$7<=$OD$5)). I can't figure out why for this row it does not seem to add up to the correct value. Any help is much appreciated.

Thanks!

 

[RoryA]

Welcome to the forum.

Unfortunately, there is no way to tell from that picture. If it works for every other row, it's almost certainly a data issue, but we can't see the data and we don't know what your sum formula is looking at.

 

[AlanY]

one thing I can think of is you have another cell(s) in column B has the same lable, e.g. another 50301-... down the column.
one way to check it is temporary rename the lable to "x" for example to see if you get the sumproduct sum right

 

[BamLindsay]

Hi Rory, thanks for your reply. Apologies, is the below screenshot better which includes the rows? The data in the grey cells is just hardcoded numbers. I am using the sumproduct formula in my first post in column OC and my manual sum in OD is just =AF+BJ+CN+DR etc up to column LT for Dec-23. There are hidden columns between each month but they do not contain the month header in row 7 so should not be double summing anything. Thanks!

 

[BamLindsay]

Hi Alan, thanks for your suggestion. I did think of that as well but all the names in column B are unique. I changed the name to 'x' and it gave the same result.

 

[Peter_SSs]

all the names in column B are unique.

Then why have this red section in the formula?
And why would the blue section need to cover all rows?
=SUMPRODUCT(($AF$14:$MX$179)*($B$14:$B$179=$B45)*($AF$7:$MX$7<=$OD$5))

Wouldn't this give the same result on row 45 with a lot less calculation?
=SUMPRODUCT((AF45:MX45)*($AF$7:$MX$7<=$OD$5))

There are hidden columns between each month but they do not contain the month header in row 7

.. but could those hidden row 7 values contain a numerical value, or even be blank? If so, those columns are likely included in the SUMPRODUCT result
Example

Now unhiding column AJ we see why the OC45 value is incorrect. I'm not saying this is your problem, just that it could be a possibility.

 

[AlanY]

forget this, my bad

 

[Peter_SSs]

Seems like those date headings occur every 30 columns so what happens if you try this formula in row 45 (& copy up/down)

=SUMPRODUCT((AF45:MX45)*($AF$7:$MX$7<=$OD$5)*(MOD(COLUMN(AF45:MX45)-COLUMN(AF45),30)=0))

 

[BamLindsay]

Thank you, Peter! that formula has worked and I have learned something new about excel which always gets me excited. I think you were right, my formula was double counting items in the hidden columns and I was failing to understand that the formula will just read a date as a number and anything less than would also be picked up even if it wasn't a date format.

Many thanks to everyone for their input.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.