Look up values in multiple cells and sum

[DebbieV]

Hi

I have a workbook with the following worksheets.

I am using the formula =SUMIF('Training'!A:A,A1,'Training'!B:B) to add
up individuals totals but I have now included another column C which
has 'yes' & 'no' response. How can I adapt the formula to only add up
the 'no' totals?

Thanks
Debbie

Example
Personal Worksheet
A B
1 Bob
2 Gail
3 Fred
4 Gae
5 Gary
Training Worksheet
A B C
1 Bob 4 Yes
2 Gail 6 No
3 Gail 1 Yes
4 Gae 5 No
5 Bob 7 Yes
6 Bob 8 Yes
7 Fred 9 No
8 Fred 5 Yes

=SUMIF('Training'!A:A,A1,'Training'!B:B)

 

[Pete_UK]

Try this:

=SUMPRODUCT((Training!A$1:A$1000=A1)*(Training!C$1:C
$1000="no")*(Training!B$1:B$1000))

Adjust the ranges to suit, but you can not have full column references
(unless you have Excel 2007).

Hope this helps.

Pete

 

[DebbieV]

Try this:

=SUMPRODUCT((Training!A$1:A$1000=A1)*(Training!C$1:C
$1000="no")*(Training!B$1:B$1000))

Adjust the ranges to suit, but you can not have full column references
(unless you have Excel 2007).

Hope this helps.

Pete






- Show quoted text -

Hi Pete

No luck. Here is the actual formula that i am using -

=SUMPRODUCT(('Step 2 - Training Details'!$A3:$A1000,B3)*('Step 2 -
Training Details'!$E3:$E1000="No")*(Step 2 - Training Details'!
$I3:$I1000))

It keeps throwing back an error on the B3 reference???

Training sheet - Col A = Surname - Col E = Yes/No - Col I = training
hours
Personal sheet Col B = Surname

Any ideas?
cheers
Debbie

 

[Dave Peterson]

How about:

=SUMPRODUCT(('Step 2 - Training Details'!$A3:$A1000=B3)
*('Step 2 - Training Details'!$E3:$E1000="No")
*(Step 2 - Training Details'!$I3:$I1000))

I changed that comma to an equal sign. Is that the comparison you wanted? (I
didn't read the whole thread.)

 

[Dave Peterson]

I copied your formula -- and it was missing an apostrophe in that last portion!!

=SUMPRODUCT(('Step 2 - Training Details'!$A3:$A1000=B3)
*('Step 2 - Training Details'!$E3:$E1000="No")
*('Step 2 - Training Details'!$I3:$I1000))