Conditional sum dependant on VLOOKUP

[David Heppell]

Hello,

I have a table that is 20 rows deep and 53 columns wide (one for each week
of the year + heading)
One of these rows specifies a person in the heading and how many hours they
have worked each week.


I have many hundreds of these tables on a worksheet and for each table I
want to pick out the row that specifies the person and
sum all hours worked by a particular grade of person that week to plot in a
graph.

Each person's grade is specified by another table(called person_vs_grade).

I have set up a table of number of hours worked by grade of person per week.

For each cell in this table I have tried to write a function that gives the
number of hours worked by that grade in the week.

the following tries to conditionally sum for grade 2

first we make sure that we only check the rows where the person's name and
their hours are (Column A is the heading column which contains the name of
the person)
summed, this is done by only summing the rows where the name lookup returns
a value.

if this is true we check that the grade of the person is equal to 2 and then
sum the appropriate column B represents week 1)


{SUM (IF (ISERROR (VLOOKUP($A$1:$A$1000, person_vs_grade, 2, FALSE)),

0, IF (VLOOKUP ($A$1:$A$1000,person_vs_grade,2,FALSE) =2,
B$1:B$1000)))}



this does not work, I am doing something really stupid,

thanks in advance for any help Dave

 

[Frank Kabel]

Hi David
after rereading your post _I'm still not sure how your spreadsheet is
designed. Problably a SUMPRODUCT function will do what you need. e.g.
=SUMPRODUCT(($A$1:$A$1000=person_name)*($B1:$B$1000))
this will sum the hours in column B for the person 'person_name'. I
have to admit I did not understood your 'grade part'. Maybe you can
expplain this with a little bit more detail

Frank

 

[David Heppell]

thanks for your reply

every peson has an assosciated grade 1 to 5

I have a table that lists every person and their grade.g

Dave 3
Ian 3
Jon 4
Craig 2


I want to sum the number of hours that people of say grade 3 have done.

so I need to lookup the person's grade to know whether to add their time,

hope this is a better explanation.

Dave

 

[Frank Kabel]

Hi David
I would use the following workaround:
1. in your data sheet add a helper column which uses VLOOKUP to
duplicate the grade for each row (lets say column H -> you can hide
this column)
2. Now use SUMPRODUCT on this sheet
=SUMPRODUCT(($A$1:$A$1000=person_name)*($B1:$B$1000)*($H$1:$H$1000=2)

HTH
Frank

 

[David Heppell]

Thanks

Hi David
I would use the following workaround:
1. in your data sheet add a helper column which uses VLOOKUP to
duplicate the grade for each row (lets say column H -> you can hide
this column)
2. Now use SUMPRODUCT on this sheet
=SUMPRODUCT(($A$1:$A$1000=person_name)*($B1:$B$1000)*($H$1:$H$1000=2)

HTH
Frank