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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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))
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top