Advice needed - SUMIFS/SUMPRODUCT Issues

Hello all,

I am trying to sum the values in a row based on certain conditions. I have the following named ranges:

Amount - this range contains the values I would like to sum
Category - the category the amount is associated with
xDate - the date an entry was made in mm/dd/yyyy format

What I'm trying to do is sum the values in the Amount range if the Category matches what is in cell A3 and the month the entry was made matches the month in cell B1. The month in cell B1 is in text format, ie: January, February, etc.


I originally tried this formula without success:

=SUMIFS(Amount,Category,$A3,xDate,TEXT(xDate,"mmmm")=B$1)

After some research I came up with this formula:

=SUMPRODUCT(--(Category=$A3),--(TEXT(xDate,"mmmm")=B$1),Amount)

This formula works, but there is a noticeable slowdown in the processing of the workbook with this formula. Other users will be entering data into this workbook, and there is a hang up each time you tab to a new cell.

So, the next thing I tried was to create a helper column on the data entry table where I put the =Text(xDate,"mmmm") formula and named that range xMonth and hid the column. With that I was able to successfully use this formula without a noticeable change in processing speed:

=SUMIFS(Amount,Campain,$A$1,Category,$A3,xMonth,B$1)

My problem with this is that if a user clicks on a row number and does a clear contents I lose the helper formula. If I try to protect just the column that contains the formula the user would get an error saying they can't make changes.

Any suggestions on a better approach?

Thanks

Yes that is what I tried. But if the user tries to clear the contents of a row by selecting the entire row by clicking on the row number it will also clears the formula even if it is hidden

Thanks TMS. So simple yet I couldn't see the forest amongst the trees. Although I had my data entry sheet set up as a structured table I was still using named ranges, and they weren't dynamic. Using the table references fixed the slowness issue.