Data Issues

[Guest]

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

 

[Govind]

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$10>28)*($B$3:$B$10<=50))
=SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$10>50)*($B$3:$B$10<=70))
=SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$10>70)*($B$3:$B$10<=100))
=SUMPRODUCT(($A$3:$A$10=A18)*($B$3:$B$10>100)*($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.

 

[Biff]

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" <[email protected]> wrote in messageHi 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>>

 

[Guest]

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.