Sumproduct Repeatability

T

Tmt

I've used this formula to calculate the frequency of items being serviced in
many departments from another worksheet. There are 2 cells that won't give me
the correct result. Please help.

=SUMPRODUCT(('Q1 '!E13:E137="Calibrated")*('Q1 '!G13:G137="70223"))
In Q1 70223 has 7 occurances which gives me a result of 7 from this
SUMPRODUCT

=SUMPRODUCT(('Q1 '!E13:E137="Calibrated")*('Q1 '!G13:G137="74532"))
In Q1 74532 has 2 occurances, but the resultance is 0 (instead of 2)
 
J

Jim Thomlinson

Most liketly the 74532 is causing you the problem. You are looking for text
occurances. If the values in the cells are numbers then they will not be
found. Try formatting the column with a decimal. The text entries will stay
the same but the numbers will include the decimal.

Try this formula to get an or condition as a way around it...
=SUMPRODUCT(('Q1 '!E13:E137="Calibrated")*('Q1 '!G13:G137="74532")+('Q1
'!G13:G137=74532))
 
L

Lars-Åke Aspelin

Most liketly the 74532 is causing you the problem. You are looking for text
occurances. If the values in the cells are numbers then they will not be
found. Try formatting the column with a decimal. The text entries will stay
the same but the numbers will include the decimal.

Try this formula to get an or condition as a way around it...
=SUMPRODUCT(('Q1 '!E13:E137="Calibrated")*('Q1 '!G13:G137="74532")+('Q1
'!G13:G137=74532))

I think there is a couple of parenthesis missing if you just want to
count the Calibrated occurances of 74532.

This is how I should write it:

=SUMPRODUCT(('Q1 '!E13:E137="Calibrated")* ( ('Q1 '!G13:G137="74532")
+ ('Q1 '!G13:G137=74532) ) )

Here is a shorter formula to achieve the same result:

=SUMPRODUCT(('Q1'!E13:E137="Calibrated")*('Q1 '!G13:G137+0=74532))

The +0 makes a number of the content in the G column even if it is
text as long at is looks like a number. Thereby you can compare it to
a number.

Hope this helps / Lars-Åke
 
T

Tmt

The longer version works: =SUMPRODUCT(('Q1 '!E13:E137="Calibrated")* ( ('Q1
'!G13:G137="74532")
+ ('Q1 '!G13:G137=74532) ) )

But the shorter formula gives me #VALUE result!

Tmt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top