SUMPRODUCT formula working on all but one worksheet

Hi Heather and welcome to the forum,

"Won't work" has a lot of possible parts to it. Here are some things to try or look for:
1. Is Calculate set to automatic or manual?
2. Are there named ranges in the formula that doesn't work?
3. What is the formula, exactly, that doesn't work?

Posting a sample workbook that shows the problem is most likely the only way we can really diagnose the problem. You can submit a sample by clicking on "Go Advanced" and then on the Paper Clip Icon (if it shows) above the advanced message area.

Hi Heather,

It looks like the person who entered Aug values didn't know about the formatting of cells and put the letters of HTG in front of the numbers. When they did this, it made the cells TEXT (value of zero) and not numbers. Excel is working correctly but it is fooling with our minds.

Excel is working correctly but the data entry person wasn't entering numbers in August, they were entering text that is always equal to zero.

If this doesn't make sense please reply. I also have a much better method of doing this problem, instead of using SumProducts...

All the values in columns D and E for August on Outpatient Data is TEXT Corrected file attached.

I selected the cells for August and did a search and replace. I searched for USD and replaced with nothing. Then I copied the formatting for the cells that I just did the search and replace on so that they would have the same cell formatting as the other cells in the column. I did the same thing for the column that is to the left of that column, replacing the prefix with nothing and then reformatting using the format painter. It worked well and the workbook that I uploaded has these changes.

Thanks for the feedback.

Glad that you have the workbook working.

I found the workbook dreadfully slow so re-did the formulae to use SUMIFS instead of SUMPRODUCT. The speed increase on my computer is dramatic. See if it works for you.

Hi Heather,

I'd do the problem like my attached. If you could get both In and Out Patient data in the same table you could produce all your answers by simply changing the month filter of a Pivot Table.

If this is something you'd like to work with, I can fix it to automatically update based on new or changed data.

Hi Heather,

I was worried that you didn't get the answers before. I'm busy this morning but can help later today. I need a little more information about what you really need and how many people are entering the data. There are several things that can help with this problem. I need to know if you are using a Mac or PC and if you can use VBA Macros. I need to know if you can do a Pivot Table instead of separate sheets. Would that be allowed? Look at your Private Messages for more directions.