Sum data based on 2 criteria
 

Hi Group

Hope you can help with this and appologies if its already been covered but I have a spreadsheet used to record the output from 4 production shifts over a number of weeks

Col A = week number
Col B = team (A,B,C,D)
Col C = Units per hour produced

The columns are named ranges i.e Col A is called "weeknum", Col B is called "Team" and Col C is called "unitsperhour"

What I need to be able to do is find the average number of units per hour for a given team and a given week so I think I'm after something along the lines of

countif(weeknum=10)and(team=B) to find the number of entries for team B in wk 10 and
sumif(weeknum=10)and(team=B),unitsperhour to add all the entries for team B in wk 10 to allow the average to be calculated.

Hope thats clear enough

Thanks in anticipation

Martin Bagshaw

Hi Martin

You can use Sumproduct

Start here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Martin B" wrote in message ...
Hi Group

Hope you can help with this and appologies if its already been covered but I have a spreadsheet used to record the output from 4 production shifts over a number of weeks

Col A = week number
Col B = team (A,B,C,D)
Col C = Units per hour produced

The columns are named ranges i.e Col A is called "weeknum", Col B is called "Team" and Col C is called "unitsperhour"

What I need to be able to do is find the average number of units per hour for a given team and a given week so I think I'm after something along the lines of

countif(weeknum=10)and(team=B) to find the number of entries for team B in wk 10 and
sumif(weeknum=10)and(team=B),unitsperhour to add all the entries for team B in wk 10 to allow the average to be calculated.

Hope thats clear enough

Thanks in anticipation

Martin Bagshaw

Do you actually need the number of entries, or did you ask for that number
just to be able to calculate the average?

Anyway, with the "WeekNum" to lookup entered in E1,
And the "Team" to lookup entered in F1,
Try these:

Number of entries for team for weeknum:
=SUMPRODUCT((weeknum=E1)*(team=F1))

Total units for team for weeknum:
=SUMPRODUCT((weeknum=E1)*(team=F1)*unitsperhour)

Average of units for team for weeknum:
=SUMPRODUCT((weeknum=E1)*(team=F1)*unitsperhour)/SUMPRODUCT((weeknum=E1)*(te
am=F1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Martin B" wrote in message
...
Hi Group

Hope you can help with this and appologies if its already been covered but I
have a spreadsheet used to record the output from 4 production shifts over a
number of weeks

Col A = week number
Col B = team (A,B,C,D)
Col C = Units per hour produced

The columns are named ranges i.e Col A is called "weeknum", Col B is called
"Team" and Col C is called "unitsperhour"

What I need to be able to do is find the average number of units per hour
for a given team and a given week so I think I'm after something along the
lines of

countif(weeknum=10)and(team=B) to find the number of entries for team B in
wk 10 and
sumif(weeknum=10)and(team=B),unitsperhour to add all the entries for team B
in wk 10 to allow the average to be calculated.

Hope thats clear enough

Thanks in anticipation

Martin Bagshaw

Thanks for responding but I can't get the formula to work, keep getting a
#NUM error message telling me the cell I am referring to containing the week
number contains a constant. Any ideas?

Martin

Martin,

Do your named ranges refer to whole columns? If so, there is your problem as
SUMPRODUCT cannot handle whole columns, only part columns.

Also Weeknum doesn't seem like a smart name for a range, it is an Analysis
Toolpak function.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Martin B" wrote in message
...

Thanks for responding but I can't get the formula to work, keep getting a
#NUM error message telling me the cell I am referring to containing the
week
number contains a constant. Any ideas?

Martin