Sumproduct Repeatability

[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)

 

[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))

 

[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

 

[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