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)
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
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,201
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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