Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (I think?)
I have two ranges A1:A100, B1:B100 each containing single digit numbers
typically between 3 and 8. (No blank cells. I would like to count all the instances of each time row B contains a number one less than that in the equivalent A cell. eg if A5 contains 6 and B5 contains 5 then that would count as one instance. The pairs have to be the same numbers. further eg A10 containing 6 and B10 containing 5 would count as a second instance but A12 containing 7 and B12 containing 6 would not. Am I making sense? TIA Sandy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (I think?)
I would create a helper column, say in Column C, with the following formula:
=IF(A1=B1+5,1,0) and fill that down, then sum column C. That sum would be your count of rows for which the value in column A is one more than the value in column C. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Sandy" wrote: I have two ranges A1:A100, B1:B100 each containing single digit numbers typically between 3 and 8. (No blank cells. I would like to count all the instances of each time row B contains a number one less than that in the equivalent A cell. eg if A5 contains 6 and B5 contains 5 then that would count as one instance. The pairs have to be the same numbers. further eg A10 containing 6 and B10 containing 5 would count as a second instance but A12 containing 7 and B12 containing 6 would not. Am I making sense? TIA Sandy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (I think?)
I mean: That sum would be your count of rows for which the value in column A
is one more than the value in column B. -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Dave F" wrote: I would create a helper column, say in Column C, with the following formula: =IF(A1=B1+5,1,0) and fill that down, then sum column C. That sum would be your count of rows for which the value in column A is one more than the value in column C. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Sandy" wrote: I have two ranges A1:A100, B1:B100 each containing single digit numbers typically between 3 and 8. (No blank cells. I would like to count all the instances of each time row B contains a number one less than that in the equivalent A cell. eg if A5 contains 6 and B5 contains 5 then that would count as one instance. The pairs have to be the same numbers. further eg A10 containing 6 and B10 containing 5 would count as a second instance but A12 containing 7 and B12 containing 6 would not. Am I making sense? TIA Sandy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (I think?)
Put these values in C1:C6 - 3, 4, 5, 6, 7, 8, then in D1 enter this
formula: =SUMPRODUCT((A$1:A$100=C1)*(B$1:B$100=C1-1)) then copy the formula down into D2:D6. It will give you the count of the number of instances where A is one more than B, for the values of A given in column C. Hope this helps. Pete On Mar 30, 4:20 pm, "Sandy" wrote: I have two ranges A1:A100, B1:B100 each containing single digit numbers typically between 3 and 8. (No blank cells. I would like to count all the instances of each time row B contains a number one less than that in the equivalent A cell. eg if A5 contains 6 and B5 contains 5 then that would count as one instance. The pairs have to be the same numbers. further eg A10 containing 6 and B10 containing 5 would count as a second instance but A12 containing 7 and B12 containing 6 would not. Am I making sense? TIA Sandy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (I think?)
One way
=SUMPRODUCT(--(A1:A100-B1:B100=1),--(A1:A100=6)) Regards, Peo Sjoblom "Sandy" wrote in message ... I have two ranges A1:A100, B1:B100 each containing single digit numbers typically between 3 and 8. (No blank cells. I would like to count all the instances of each time row B contains a number one less than that in the equivalent A cell. eg if A5 contains 6 and B5 contains 5 then that would count as one instance. The pairs have to be the same numbers. further eg A10 containing 6 and B10 containing 5 would count as a second instance but A12 containing 7 and B12 containing 6 would not. Am I making sense? TIA Sandy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct (I think?)
Thank you thats perfect
Sandy "Peo Sjoblom" wrote in message ... One way =SUMPRODUCT(--(A1:A100-B1:B100=1),--(A1:A100=6)) Regards, Peo Sjoblom "Sandy" wrote in message ... I have two ranges A1:A100, B1:B100 each containing single digit numbers typically between 3 and 8. (No blank cells. I would like to count all the instances of each time row B contains a number one less than that in the equivalent A cell. eg if A5 contains 6 and B5 contains 5 then that would count as one instance. The pairs have to be the same numbers. further eg A10 containing 6 and B10 containing 5 would count as a second instance but A12 containing 7 and B12 containing 6 would not. Am I making sense? TIA Sandy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Help | Excel Worksheet Functions | |||
SUMPRODUCT Help | Excel Worksheet Functions |