Sum data based on 2 criteria

  • Thread starter Thread starter Martin B
  • Start date Start date
M

Martin B

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
mn.bagshaw<nospam>@btinternet.com
 
Hi Martin

You can use Sumproduct

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



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


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
mn.bagshaw<nospam>@btinternet.com
 
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 !
---------------------------------------------------------------------------



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
mn.bagshaw<nospam>@btinternet.com
 
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)
 
Back
Top