# SumProducts not working correctly....

#### Terrick

##### New Member
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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))

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:
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?

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!

Using leaves me with # instead of a blank cell.

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

Why don't you just replace # in the formula with ""?

Why don't you just replace # in the formula with ""?

I did just that, I edited my post I think after you posted. Thank you for your help!

I did just that, I edited my post I think after you posted. Thank you for your help!

You are welcome.

Replies
4
Views
181
Replies
3
Views
332
Replies
4
Views
706
Replies
7
Views
296
Replies
1
Views
131

1,203,140
Messages
6,053,727
Members
444,681
Latest member

### 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.

### Which adblocker are you using?

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

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