Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Repeatability
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Repeatability
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)) -- HTH... Jim Thomlinson "Tmt" wrote: 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Repeatability
On Tue, 9 Feb 2010 13:11:01 -0800, Jim Thomlinson
wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Repeatability
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 "Lars-Ã…ke Aspelin" wrote: On Tue, 9 Feb 2010 13:11:01 -0800, Jim Thomlinson wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Coefficient of Repeatability | Excel Worksheet Functions | |||
How do I use Excel to do a Gage Repeatability and Reproducibility. | Excel Worksheet Functions | |||
How do I calculate repeatability using Excel? | Excel Discussion (Misc queries) |