| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up values in multiple cells and sum
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look up values in multiple cells and sum
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look up values in multiple cells and sum
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Look up values in multiple cells and sum
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)) Dave Peterson wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find values in multiple cells and paste row values | Excel Discussion (Misc queries) | |||
| Counting multiple values within single cells | Excel Worksheet Functions | |||
| Looking up values in multiple cells | Excel Discussion (Misc queries) | |||
| offsetting values of multiple cells! | Excel Worksheet Functions | |||
| Values for Y and N in multiple cells | Excel Discussion (Misc queries) | |||
Linear Mode
