Need assistance with Sum Product formula

[Ginaf]

I am attempting to populate values from the "LMPA" tab to the "stats" tab as per the attached spreadsheet. For instance, stat 3 for outpatient revenue (highlighted in blue) for dept. 5100 (highlighted in green) is .91 (cell D4) as per the "LMPA" tab. I would like to populate the .91 stat 3 value (cell C3 ) in the "stats" tab I attempted to use the Sum Product formula but it doesn't work. I believe I know why but I am not sure what formula to use.

Thank you for your help.
Gina

 

[Nunes]

hi there!

there's nothing wrong with your sumproduct.
the problema lies in the N/A's in cells CX4 and CY4 of the "LMPA" sheet.

you have 2 ways to solve this:
either you replace those N/A's by some number (zeros i guess?) or you can replace the:
LMPA!$C$4:$DF$4
part of your sumproduct by
IFERROR(LMPA!$C$4:$DF$4;0)

I believe this will solve your problem for the time being.

best
Nuno

 

[Ginaf]

hi there!

there's nothing wrong with your sumproduct.
the problema lies in the N/A's in cells CX4 and CY4 of the "LMPA" sheet.

you have 2 ways to solve this:
either you replace those N/A's by some number (zeros i guess?) or you can replace the:
LMPA!$C$4:$DF$4
part of your sumproduct by
IFERROR(LMPA!$C$4:$DF$4;0)

I believe this will solve your problem for the time being.

best
Nuno

Thank you so much for your prompt reply Nuno. I replaced the NA's with zero and it worked. I really appreciate it. Have a great day!

 

[Somendra Misra]

Hi,

Another alternatives can be :

1. Use below formula in C3 and copy down & Across
=SUMPRODUCT((LMPA!$C$2:$DF$2=$A3)*(LMPA!$C$3:$DF$3=C$2),(LMPA!$C$4:$DF$4))

2. Use below formula in C3 and copy down & across:
=SUMIFS(LMPA!$C$4:$DL$4,LMPA!$C$2:$DL$2,Stats!$A3,LMPA!$C$3:$DL$3,Stats!C$2)

Regards,

 

[Ginaf]

Hi,

Another alternatives can be :

1. Use below formula in C3 and copy down & Across
=SUMPRODUCT((LMPA!$C$2:$DF$2=$A3)*(LMPA!$C$3:$DF$3=C$2),(LMPA!$C$4:$DF$4))

2. Use below formula in C3 and copy down & across:
=SUMIFS(LMPA!$C$4:$DL$4,LMPA!$C$2:$DL$2,Stats!$A3,LMPA!$C$3:$DL$3,Stats!C$2)

Regards,

Thank you Somendra. I believe I already used the first formula in my cell but I will definitely keep your second suggestion in mind. Thank you so much for your reply. Have a great day! Gina