sumproduct not working

[ysherriff]

i need help. for some reason my sumproduct is not working. the ranges are the same and i for the life of me can not seem to know what is happening. i can not use sumifs because the format has to be 2003 compatible. i tried array formula using sum and if and still not working. thank you very much.

the formula is on sheet titled" calculator".


Thank you very much...

 

[Ramesh Deo]

@ysherriff,

Try this if this help
=SUMPRODUCT(--(ADMIT_PSR_NAME=$B14),--(ADMIT_PSR_LC=$C14),(ADMIT_MEDICARE))

 

[Asheesh]

Array solution...use it in cell E14...
SUM(IF(ADMIT_PSR_NAME=$B14,IF(ADMIT_PSR_LC=$C14,(ADMIT_MEDICARE))))

 

[NARAYANK991]

Hi ,

The 14th and the 20th values from the bottom of column F had spaces in them.

Delete those spaces , and you should be able to see all the results.

Narayan

 

[NARAYANK991]

Hi ,

See your file now. Some of the other columns also had spaces in some cells.

Narayan

 

[prasaddn]

Hi,

there were spaces " " in couple of cells in ADMITS sheets. By removing those spaces your file and formula seems to be working as expected.

attached is the spaces removed file. I have highlighted the same in yellow for you to crosscheck in your original file.

Regards,
Prasad DN

 

[ysherriff]

thank you all. I tried ramesh solution and it works. But Naraya, where are the spaces and what sheet. I checked to make sure their were no spaces in the table.

Thanks for your help

 

[ysherriff]

okay. i am till dumbfounded and thanks prsa but in reviewing both spreadsheets, where are the spaces? I see you highlighted it yellow Prsa but there is not difference between yours and mine.

 

[NARAYANK991]

Hi ,

Use the =ISTEXT(...) formula to find out the space characters ; where a cell is blank or contains a number , this formula will return FALSE ; where there is even one space character , the formula will return TRUE.

Narayan

 

[jeffreyweir]

ysherrif: the layout of your Calculator sheet looks a lot like the layout of a PivotTable. Maybe you can just use a PivotTable in the first place.

 

[ysherriff]

great suggestion. but with pivot table, i have to refresh the underlying data all the time. with this structure, i only have to copy and paste. more scaleable even though pivot from a design standpoint is better.

thanks

 

[jeffreyweir]

Note that you can make the pivottable refresh automatically any time you add new data with a very simple macro. So if you only refresh the data a coulpe of times a day or less, then a PivotTable is a much better option than your current approach.

If you end up with many SUMPRODUCTS or SUMIFS or Array Formula that point at thousands of rows, then your spreadsheet will become very slow.

 

[ysherriff]

You are correct. this is atemplate that only gets updated weekly so i can run a macro to refresh pivot when i run the consolidation macro.

good point jeff !!!!

 

[ysherriff]

do you happen to have the macro so i don't have to recreate? If not, no big deal.

Thanks

 

[Faseeh]

@ysheriff,

I guess you can turn the data that is being feed to pivot into a "Table" that can be refreshed with a right click without any macros!

 

[jeffreyweir]

Faseeh - ysheriff is using Excel 2003, which has lists but not tables.

 

[Faseeh]

@jeffreyweir - Humm I missed that.. thank you!

 

[jeffreyweir]

@ysherriff: there's many ways to code this up. Unfortunately I have Excel 2013 not 2003, so some of the ways I know won't work on your file, and so I'm flying a bit blind.

But try the attached file. I've screwed up your formatting, but this should give you the general idea of how a pivot very easily accomplishes what you're after. In excel 2010 and later, pivots have much nicer formatting. It is possible to format them like your report in 2003 but I haven't done so here.

Furthermore, in 2007 and later, Excel has something called Tables that you can connect pivots/charts/formulas to, which autoexpand to accommodate new data. In 2003 there was something called Excel Lists, and if you were to connect a pivot to an Excel List then all you need to do after you make any changes to the list is to refresh the pivot, and the new data would appear in the pivot without the need to click the 'Change Data Source' button for the pivot.

In the attached file, hopefully I've constructed a 2003-compatable Excel List out of your source data. I have a sheet change event that fires whenever you select the Pivot sheet, which refreshes the pivot.

That should be enough to get you started, if you want to progress down this route further. I personally will always use a Pivot in preference to SUMPRODUCTS etc if I can.

If the macro doesn't work for you, post back and I'm sure we can find someone with Excel 2003 installed to help you out.