If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to count data in a column that falls between a range
i am trying to count the number of times a number between a range appears in
a column e.g. in sheet 1 i have column1 Column2 2000 4000 3000 10000 4000 2000 5000 6000 in sheet 2 i have the criteria A) starts at 1000 and finishes at 1999 B) starts at 2000 and finishes at 3999 C) starts at 4000 and finishes at 10000 in sheet 3 i am trying to summaries sheet 1 data on the sheet 2 criteria Column 1 Column2 A = 0 0 B = 2 1 C = 2 3 Does anyone know of a count formula i can use to do this |
#2
|
|||
|
|||
Formula to count data in a column that falls between a range
Hi,
In sheet 3 enter for criteria A =countif(sheet1!A1:A10000,"=1999") copy right to get column B for criteria B use =countifis(sheet1!A1:A10000,"1999",sheet1!A1:A100 00,"=3999") Criteria C =countif(sheet1!A1:A10000,"3999) "Paul_UK" wrote: i am trying to count the number of times a number between a range appears in a column e.g. in sheet 1 i have column1 Column2 2000 4000 3000 10000 4000 2000 5000 6000 in sheet 2 i have the criteria A) starts at 1000 and finishes at 1999 B) starts at 2000 and finishes at 3999 C) starts at 4000 and finishes at 10000 in sheet 3 i am trying to summaries sheet 1 data on the sheet 2 criteria Column 1 Column2 A = 0 0 B = 2 1 C = 2 3 Does anyone know of a count formula i can use to do this |
#3
|
|||
|
|||
Formula to count data in a column that falls between a range
Thanks but not what i am looking for it to do, the Value say 2000 as in
criteria A may change so i am looking for the formula to change the count result if the criteria in sheet 2 changes without having to go back into the formula and change the = number "Eduardo" wrote: Hi, In sheet 3 enter for criteria A =countif(sheet1!A1:A10000,"=1999") copy right to get column B for criteria B use =countifis(sheet1!A1:A10000,"1999",sheet1!A1:A100 00,"=3999") Criteria C =countif(sheet1!A1:A10000,"3999) "Paul_UK" wrote: i am trying to count the number of times a number between a range appears in a column e.g. in sheet 1 i have column1 Column2 2000 4000 3000 10000 4000 2000 5000 6000 in sheet 2 i have the criteria A) starts at 1000 and finishes at 1999 B) starts at 2000 and finishes at 3999 C) starts at 4000 and finishes at 10000 in sheet 3 i am trying to summaries sheet 1 data on the sheet 2 criteria Column 1 Column2 A = 0 0 B = 2 1 C = 2 3 Does anyone know of a count formula i can use to do this |
#4
|
|||
|
|||
Formula to count data in a column that falls between a range
Hi Paul,
I understan now so in sheet2 where you want the criteria just enter for example if your criteria is as explained in A1 enter 1999, in B1 3999 then modify the formulas changing 1999 for A1 and 3999 for B1 "Paul_UK" wrote: Thanks but not what i am looking for it to do, the Value say 2000 as in criteria A may change so i am looking for the formula to change the count result if the criteria in sheet 2 changes without having to go back into the formula and change the = number "Eduardo" wrote: Hi, In sheet 3 enter for criteria A =countif(sheet1!A1:A10000,"=1999") copy right to get column B for criteria B use =countifis(sheet1!A1:A10000,"1999",sheet1!A1:A100 00,"=3999") Criteria C =countif(sheet1!A1:A10000,"3999) "Paul_UK" wrote: i am trying to count the number of times a number between a range appears in a column e.g. in sheet 1 i have column1 Column2 2000 4000 3000 10000 4000 2000 5000 6000 in sheet 2 i have the criteria A) starts at 1000 and finishes at 1999 B) starts at 2000 and finishes at 3999 C) starts at 4000 and finishes at 10000 in sheet 3 i am trying to summaries sheet 1 data on the sheet 2 criteria Column 1 Column2 A = 0 0 B = 2 1 C = 2 3 Does anyone know of a count formula i can use to do this |
Thread Tools | |
Display Modes | |
|
|