sumproduct ignoring errors

[rayk8301]

Hello,

I have been having some issues getting sumproduct to work and ignores errors and only focus on numbered cells. Example is I'm trying to get the weighted average of average handle times against the number of calls taken per person. Example sumproduct is = sumproduct(handle times, # of calls)/sum(# of calls))

any help is appreciated!

 

[Joe4]

Re: sumproduct ignorning errors

What is the cells returning errors? Are they formulas? If so, what does the formula look like?
Usually the best way to handle errors is to handle them at the source (instead of ignoring them). So if we can update some formulas to make them go away, that might be our best bet.

 

[Special-K99]

Re: sumproduct ignorning errors

Fix the errors in the first place seems a sensible start.

 

[rayk8301]

Re: sumproduct ignorning errors

the errors are there for a reason, it's the only way I can get the graph lines to stop at the numbers and not continue on due to the errors, that's why I'm looking for a sumproduct, perhaps with a combination of isnumber or iserror to ignore those and return the result.

 

[Joe4]

Re: sumproduct ignorning errors

There are various different error codes that Excel might return. Which one are you seeing?
Also, please post your current SUMPRODUCT formula (that will at least let us know what ranges you are looking at), and let us know what range these possible errors exist in.

 

[rayk8301]

Re: sumproduct ignorning errors

#NAME ? is the error.

In the calls taken column (column J) is where these errors occur, the other column (k:k - average handle time) doesn't have errors. so the formula that I want to use is

=sumproduct(k:k,j:j)/sum(j:j)

 

[Joe4]

Re: sumproduct ignorning errors

Since you are using SUMPRODUCT, I think it may need you to use an array formula, which is really not my strong suit. Maybe someone else will be able to chime in and help out.
In the meantime, you can do a Google search on "excel sumproduct ignore errors" and take a look at some of the ways people tried to handle this. Most of the ones I found were addressing the #N/A or #VALUE errors.

 

[rayk8301]

Re: sumproduct ignorning errors

Thanks for your input anyway. Appreciate it. I ended up just making another column next to J and calling out each of the cells if they are numbers and "" if not, then using that new column in my sumproduct formula which did the trick. thanks again

 

[Marcelo Branco]

Re: sumproduct ignorning errors

See if this does what you want


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Values​

[/TD]
[TD]

Weight​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

12​

[/TD]
[TD]

1​

[/TD]
[TD]

18,76923​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

14​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

#NOME?​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

18​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

#NOME?​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

22​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in K2
=SUM(IF(ISNUMBER(I2:I7),I2:I7*J2:J7))/SUM(IF(ISNUMBER(I2:I7),J2:J7))
Ctrl+Shift+Enter

Remark: for the sake of performance do not use references to entire columns in array formulas.

M,

 

[rayk8301]

Re: sumproduct ignorning errors

this didn't work. i have to divide by I, not J. I switched in different ways but still nothing. No worries though thank you.