Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data Issues
Hi my name is Paul and I am agonising over a data/formual problem. I have the
folowing spreadsheet. Name Age Mike 28 Mike 96 Dave 52 Dave 12 Dave 68 Jenny 75 Craig 129 Craig 2 This sheet is summarised in another worksheet which has the following headings Name <28 <50 <70 <100 <120 Dave Jenny Mike Craig What I am trying to do is insert formulas into the summary sheet under the (<) colums which will count how many daves are 28 or older but not more than 50. I use a formula which can count how many daves for examle are < a number, but it cannot seem to return how many daves are < a number but less than another number. the formula is SUMIF and I got it from the help menu. I would be sooo stoked if you could help me with this.. Thanks |
#2
|
|||
|
|||
Data Issues
Hi,
Try using the formulas =SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$10<=28)) =SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$1028)*($B$3 :$B$10<=50)) =SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$1050)*($B$3 :$B$10<=70)) =SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$1070)*($B$3 :$B$10<=100)) =SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$10100)*($B$ 3:$B$10<=120)) to get the count of employees less than or equal to 28, above 28 and below 50, above 50 and below 70, above 70 and below 100 and above 100 and below 120 respectively. Its presumed that the original data is in the range A3 to B10. Change the ranges accordingly. Regards Govind. Paul wrote: Hi my name is Paul and I am agonising over a data/formual problem. I have the folowing spreadsheet. Name Age Mike 28 Mike 96 Dave 52 Dave 12 Dave 68 Jenny 75 Craig 129 Craig 2 This sheet is summarised in another worksheet which has the following headings Name <28 <50 <70 <100 <120 Dave Jenny Mike Craig What I am trying to do is insert formulas into the summary sheet under the (<) colums which will count how many daves are 28 or older but not more than 50. I use a formula which can count how many daves for examle are < a number, but it cannot seem to return how many daves are < a number but less than another number. the formula is SUMIF and I got it from the help menu. I would be sooo stoked if you could help me with this.. Thanks |
#3
|
|||
|
|||
Data Issues
Hi!
You're summary table is setup in such a way that the last criteria "<120" would need a separate formula since there is no upper boundary beyond 120. Assume: Name Age Mike 28 Mike 96 Dave 52 Dave 12 Dave 68 Jenny 75 Craig 129 Craig 2 Is on Sheet1 in the range A1:B10. A1 and B1 are the headers: Name, Age. A2 and B2 are empty and the data starts in A3:B3 On Sheet2..... A1:F1 are the headers: Name,28,50,70,100,120 You should get rid of the "<". Sheet2 A3:A6 are the listed names: Dave, Jenny,Mike ,Craig In Sheet2 B3 enter this formula and copy across to E3: =SUMPRODUCT(--(Sheet1!$A$3:$A$10=$A3),--(Sheet1!$B$3:$B$10=B$1),--(Sheet1!$B$3:$B$10<C$1))Enter this formula in F3:=SUMPRODUCT(--(Sheet1!$A$3:$A$10=$A3),--(Sheet1!$B$3:$B$10=F$1))Now, select the range B3:F3 and copy down to row 6.Biff"Paul" wrote in ... Hi my name is Paul and I am agonising over a data/formual problem. I havethe folowing spreadsheet. Name Age Mike 28 Mike 96 Dave 52 Dave 12 Dave 68 Jenny 75 Craig 129 Craig 2 This sheet is summarised in another worksheet which has the followingheadings Name <28 <50 <70 <100 <120 Dave Jenny Mike Craig What I am trying to do is insert formulas into the summary sheet under the (<) colums which will count how many daves are 28 or older but not morethan 50. I use a formula which can count how many daves for examle are < a number, but it cannot seem to return how many daves are < a number but less than another number. the formula is SUMIF and I got it from the help menu. I would be sooo stoked if you could help me with this.. Thanks |
#4
|
|||
|
|||
Data Issues
Can you just subtract the sum of the prior columns of the same row? Ex: in
B2 you have a formula to find the number of Daves <28. In C2 you have a formula to find the number of Daves <50. Append to that formula -SUM(B$2:B2). Likewise in D2, you have a formula to find the number of Daves <70; append -SUM(B$2:B3). And so on. So you're always calculating the running total using the SUMIF, then subtracting out with SUM the total in lower-age buckets. "Paul" wrote: Hi my name is Paul and I am agonising over a data/formual problem. I have the folowing spreadsheet. Name Age Mike 28 Mike 96 Dave 52 Dave 12 Dave 68 Jenny 75 Craig 129 Craig 2 This sheet is summarised in another worksheet which has the following headings Name <28 <50 <70 <100 <120 Dave Jenny Mike Craig What I am trying to do is insert formulas into the summary sheet under the (<) colums which will count how many daves are 28 or older but not more than 50. I use a formula which can count how many daves for examle are < a number, but it cannot seem to return how many daves are < a number but less than another number. the formula is SUMIF and I got it from the help menu. I would be sooo stoked if you could help me with this.. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |