Look up values in multiple cells and sum
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.)
DebbieV wrote:
On Feb 4, 8:22 pm, Pete_UK wrote:
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
On Feb 4, 8:53 am, DebbieV wrote:
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)- Hide quoted text -
- 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
|