If Statement Returning a 0 in Google Sheets but Calculates Correctly in Excel

[ndg4405]

We have a bunch of raw data dumps with sales info and I am trying to build a breakdown so we can determine how much of each products monthly sales were organic versus email promotion versus rebates, etc.

So you can see the image above. The if statement goes like this: If BreakDown="units" then sumif to match product name and month and year to get the total units sold. The false part of the if statement gives the units as a percentage of the total. So it's the same sumif formula but then divided by the total in row 19.

This is the formula in cell H15 so June PPC:

=iferror(IF($L$1="Units",SUMIFS('Monthly Sales Raw Data'!$AE:$AE,'Monthly Sales Raw Data'!$A:$A,$B14,'Monthly Sales Raw Data'!$B:$B,H$4,'Monthly Sales Raw Data'!$E:$E,$H$1),SUMIFS('Monthly Sales Raw Data'!$AE:$AE,'Monthly Sales Raw Data'!$A:$A,$B14,'Monthly Sales Raw Data'!$B:$B,H$4,'Monthly Sales Raw Data'!$E:$E,$H$1)/H$19),0)

Here is what happens when you change it to Percentage in the breakdown:

Instead of returning 132/419 it just gives a 0. It does this for every cell.

I have broken the formula out from the if statement and it works in all of it's component parts. It also works perfectly in excel. For some reason Google Sheets is not liking this if statement. I even tried changing the Breakdown away from text into a number like if it equals 1 sum the units, otherwise do the percent. That didn't work either.

I am completely stumped here because to me all the logic checks out and like I said it works in component parts and in excel. Is this a Google Sheets limitation? Any way around this to get it to work?

Thank you in advance for your help!

 

[sandy666]

Google Sheets - Google Docs Editors Help

 

[ndg4405]

Thanks sandy, but I know how to use excel very well. I'm just stuck at the moment and asking for help, not a link to the full instructions guide on learning excel from scratch

 

[Norie]

What do you get if you remove the IFERROR function the formula is wrapped in?

 

[sandy666]

Thanks sandy, but I know how to use excel very well. I'm just stuck at the moment and asking for help, not a link to the full instructions guide on learning excel from scratch

this is not excel guide but google sheet

 

[ndg4405]

What do you get if you remove the IFERROR function the formula is wrapped in?

Same thing. I just added the if error in there because I was trying to figure out if that would change anything.

The only way to divide to 0 would be that the top sum part needs to equal 0 but it doesn't which is why I am so confused. If I just pull that piece of the formula out it works on it's own. It is literally just adding in the if statement which is making the results mess up.

 

[Fluff]

Are your cells formatted to show decimal values?

 

[ndg4405]

Are your cells formatted to show decimal values?

Thank You!

This was literally driving me insane. the difference was in excel you can do conditional number formating so I had units as zero decimals and percentage formatted as a percentage. You can't do conditional number formatting in sheets so it was set to no decimal points and that's why every percent rounded down to 0

Can't believe this was right in front of my face. I really appreciate your help on this one

 

[RoryA]

It looks like you could simplify that to:

=iferror(SUMIFS('Monthly Sales Raw Data'!$AE:$AE,'Monthly Sales Raw Data'!$A:$A,$B14,'Monthly Sales Raw Data'!$B:$B,H$4,'Monthly Sales Raw Data'!$E:$E,$H$1)/

IF($L$1="Units",1,​

H$19),0)
​

 

[Fluff]

Glad we could help & thanks for the feedback.