SumProducts not working correctly....

Terrick

New Member
Joined
Jun 3, 2015
Messages
47
Good evening all,

I currently ran into an issue, as soon as I solved my last one! (but of course!)

I'm currently using this formula to get information from one sheet to the next,
=TEXT(SUMIFS('Raw Data'!$C:$C,'Raw Data'!$A:$A,"1",'Raw Data'!$B:$B,$A14),"#")

Now, when I go to use "SumProducts" at the end of my sheet it doesn't work correctly unless I take that formula above and divide by 1. Now when I divide by 1, I get a #VALUE error if the cell is left empty.

Is there a reason excel is making me divide by 1 to show a number in my sumproducts cell?

SumProduct formula:
=SUMPRODUCT($B$4:$AY$4,B14:AY14)
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
If you are using the TEXT function to return the integer value of the SUMIFS result, try using the INT function instead.

=INT(SUMIFS('Raw Data'!$C:$C,'Raw Data'!$A:$A,"1",'Raw Data'!$B:$B,$A14))
 

Terrick

New Member
Joined
Jun 3, 2015
Messages
47
If you are using the TEXT function to return the integer value of the SUMIFS result, try using the INT function instead.

=INT(SUMIFS('Raw Data'!$C:$C,'Raw Data'!$A:$A,"1",'Raw Data'!$B:$B,$A14))


That gets what I need, but the INT function puts a 0 where the cell is blank. Is there a way to make INT function keep a blank cell instead of filling it with 0?

I was mainly using TEXT function to leave the cell blank if it was a 0.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
1. Do not quote 1.

2. Try...

=IFERROR(1/(1/SUMIFS('Raw Data'!$C:$C,'Raw Data'!$A:$A,1,'Raw Data'!$B:$B,$A14)),"#")

Does [2] help?
 

Terrick

New Member
Joined
Jun 3, 2015
Messages
47

ADVERTISEMENT

1. Do not quote 1.

2. Try...

=IFERROR(1/(1/SUMIFS('Raw Data'!$C:$C,'Raw Data'!$A:$A,1,'Raw Data'!$B:$B,$A14)),"#")

Does [2] help?

Using
=IFERROR(1/(1/SUMIFS('Raw Data'!$C:$C,'Raw Data'!$A:$A,1,'Raw Data'!$B:$B,$A14)),"#")
leaves me with # instead of a blank cell.

If I just remove the # from quotations, it works correctly. Thank you!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,157
Messages
5,835,715
Members
430,382
Latest member
legolas97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top