| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing 2 ifs
I need to add the A column if B has M and C has 1, and also for the 2's in C
if M & the 3's in C if M. I need 24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A B C 8 M 1 8 M 1 8 M 1 6.14 #N/A 1 8 M 2 8 M 2 10 M 2 10.05 M 2 8 #N/A 2 8 M 3 10.5 M 3 8 M 3 8 M 3 8 M 3 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing 2 ifs
Try this:
1's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100=1),A1:A100) 2's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100=2),A1:A100) 3's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100=3),A1:A100) HTH, Paul -- "Steve" wrote in message ... I need to add the A column if B has M and C has 1, and also for the 2's in C if M & the 3's in C if M. I need 24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A B C 8 M 1 8 M 1 8 M 1 6.14 #N/A 1 8 M 2 8 M 2 10 M 2 10.05 M 2 8 #N/A 2 8 M 3 10.5 M 3 8 M 3 8 M 3 8 M 3 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing 2 ifs
In column D you can record these values:
D1: 1 D2: 2 D3: 3 Then put this formula in E1: =SUMPRODUCT((C$1:C$20=D1)*(B$1:B$20="M")*(A$1:A$20 )) and copy it down into E2:E3. Adjust ranges if you have more than 20 rows of data. Hope this helps. Pete On Mar 11, 8:13*pm, Steve wrote: I need to add the A column if B has M and C has 1, and also for the 2's in C if M & the 3's in C if M. I need *24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A * * * * * * * *B * * * * * * C 8 * * * M * * * 1 8 * * * M * * * 1 8 * * * M * * * 1 6.14 * *#N/A * *1 8 * * * M * * * 2 8 * * * M * * * 2 10 * * *M * * * 2 10.05 * M * * * 2 8 * * * #N/A * *2 8 * * * M * * * 3 10.5 * *M * * * 3 8 * * * M * * * 3 8 * * * M * * * 3 8 * * * M * * * 3 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing 2 ifs
Thanks guys, but I'm not able to get either suggestion to work. I think it
may be because Columns B & C are formula references as follows: B = VLOOKUP(E3,'data'!$B$2:$G$50,6,FALSE) and C is =LEFT(D3,1) "Pete_UK" wrote: In column D you can record these values: D1: 1 D2: 2 D3: 3 Then put this formula in E1: =SUMPRODUCT((C$1:C$20=D1)*(B$1:B$20="M")*(A$1:A$20 )) and copy it down into E2:E3. Adjust ranges if you have more than 20 rows of data. Hope this helps. Pete On Mar 11, 8:13 pm, Steve wrote: I need to add the A column if B has M and C has 1, and also for the 2's in C if M & the 3's in C if M. I need 24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A B C 8 M 1 8 M 1 8 M 1 6.14 #N/A 1 8 M 2 8 M 2 10 M 2 10.05 M 2 8 #N/A 2 8 M 3 10.5 M 3 8 M 3 8 M 3 8 M 3 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing 2 ifs
Looks like C is being treated as text.
Try this: (basically, put quotes around the numbers for column C. 1's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="1"),A1:A100) 2's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="2"),A1:A100) 3's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="3"),A1:A100) HTH, Paul "Steve" wrote in message ... Thanks guys, but I'm not able to get either suggestion to work. I think it may be because Columns B & C are formula references as follows: B = VLOOKUP(E3,'data'!$B$2:$G$50,6,FALSE) and C is =LEFT(D3,1) "Pete_UK" wrote: In column D you can record these values: D1: 1 D2: 2 D3: 3 Then put this formula in E1: =SUMPRODUCT((C$1:C$20=D1)*(B$1:B$20="M")*(A$1:A$20 )) and copy it down into E2:E3. Adjust ranges if you have more than 20 rows of data. Hope this helps. Pete On Mar 11, 8:13 pm, Steve wrote: I need to add the A column if B has M and C has 1, and also for the 2's in C if M & the 3's in C if M. I need 24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A B C 8 M 1 8 M 1 8 M 1 6.14 #N/A 1 8 M 2 8 M 2 10 M 2 10.05 M 2 8 #N/A 2 8 M 3 10.5 M 3 8 M 3 8 M 3 8 M 3 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing 2 ifs
I still can't get it to work, so I'll understand if you're becoming
impatient, and have other things to do. But just in case, here is the actual data and refs, to better understand: S3 and down has this formula =VLOOKUP(E3,Data!$B$2:$G$50,6,FALSE) U3 and down has this formula =LEFT(D3,1) I need to add up all the U1s with S column Ms in S so I get 30.14 The U2s with S column Ms so I get 36.05 and U3s with S column Ms so I get 26.5. The #N/As are not needed. Again, much appreciated if it can be fixed. D E F N S U 112 4444 Montana 8 M 1 112 5555 Farve 8 M 1 122 6666 Aiken 8 M 1 198 7777 Manning 6.14 M 1 212 9999 Young 8 M 2 212 8888 Unitas 8 #N/A 2 212 9991 Starr 10 M 2 212 8884 Elway 10.05 M 2 222 6664 Rice 8 M 2 312 2223 Payton 8 M 3 312 1245 Brown 10.5 M 3 312 1478 Sayers 8 M 3 312 4587 Morino 8 #N/A 3 312 5689 taylor 8 #N/A 3 "PCLIVE" wrote: Looks like C is being treated as text. Try this: (basically, put quotes around the numbers for column C. 1's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="1"),A1:A100) 2's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="2"),A1:A100) 3's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="3"),A1:A100) HTH, Paul "Steve" wrote in message ... Thanks guys, but I'm not able to get either suggestion to work. I think it may be because Columns B & C are formula references as follows: B = VLOOKUP(E3,'data'!$B$2:$G$50,6,FALSE) and C is =LEFT(D3,1) "Pete_UK" wrote: In column D you can record these values: D1: 1 D2: 2 D3: 3 Then put this formula in E1: =SUMPRODUCT((C$1:C$20=D1)*(B$1:B$20="M")*(A$1:A$20 )) and copy it down into E2:E3. Adjust ranges if you have more than 20 rows of data. Hope this helps. Pete On Mar 11, 8:13 pm, Steve wrote: I need to add the A column if B has M and C has 1, and also for the 2's in C if M & the 3's in C if M. I need 24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A B C 8 M 1 8 M 1 8 M 1 6.14 #N/A 1 8 M 2 8 M 2 10 M 2 10.05 M 2 8 #N/A 2 8 M 3 10.5 M 3 8 M 3 8 M 3 8 M 3 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing 2 ifs
Steve,
If you want to send me the spreadsheet I can take a look at it. -- "Steve" wrote in message ... I still can't get it to work, so I'll understand if you're becoming impatient, and have other things to do. But just in case, here is the actual data and refs, to better understand: S3 and down has this formula =VLOOKUP(E3,Data!$B$2:$G$50,6,FALSE) U3 and down has this formula =LEFT(D3,1) I need to add up all the U1s with S column Ms in S so I get 30.14 The U2s with S column Ms so I get 36.05 and U3s with S column Ms so I get 26.5. The #N/As are not needed. Again, much appreciated if it can be fixed. D E F N S U 112 4444 Montana 8 M 1 112 5555 Farve 8 M 1 122 6666 Aiken 8 M 1 198 7777 Manning 6.14 M 1 212 9999 Young 8 M 2 212 8888 Unitas 8 #N/A 2 212 9991 Starr 10 M 2 212 8884 Elway 10.05 M 2 222 6664 Rice 8 M 2 312 2223 Payton 8 M 3 312 1245 Brown 10.5 M 3 312 1478 Sayers 8 M 3 312 4587 Morino 8 #N/A 3 312 5689 taylor 8 #N/A 3 "PCLIVE" wrote: Looks like C is being treated as text. Try this: (basically, put quotes around the numbers for column C. 1's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="1"),A1:A100) 2's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="2"),A1:A100) 3's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="3"),A1:A100) HTH, Paul "Steve" wrote in message ... Thanks guys, but I'm not able to get either suggestion to work. I think it may be because Columns B & C are formula references as follows: B = VLOOKUP(E3,'data'!$B$2:$G$50,6,FALSE) and C is =LEFT(D3,1) "Pete_UK" wrote: In column D you can record these values: D1: 1 D2: 2 D3: 3 Then put this formula in E1: =SUMPRODUCT((C$1:C$20=D1)*(B$1:B$20="M")*(A$1:A$20 )) and copy it down into E2:E3. Adjust ranges if you have more than 20 rows of data. Hope this helps. Pete On Mar 11, 8:13 pm, Steve wrote: I need to add the A column if B has M and C has 1, and also for the 2's in C if M & the 3's in C if M. I need 24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A B C 8 M 1 8 M 1 8 M 1 6.14 #N/A 1 8 M 2 8 M 2 10 M 2 10.05 M 2 8 #N/A 2 8 M 3 10.5 M 3 8 M 3 8 M 3 8 M 3 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Summing 2 ifs
You might try changing your formula to:
=LEFT(D3,1)*1 Then leave off the quotes around the numbers as previously recommended. -- "PCLIVE" wrote in message ... Steve, If you want to send me the spreadsheet I can take a look at it. -- "Steve" wrote in message ... I still can't get it to work, so I'll understand if you're becoming impatient, and have other things to do. But just in case, here is the actual data and refs, to better understand: S3 and down has this formula =VLOOKUP(E3,Data!$B$2:$G$50,6,FALSE) U3 and down has this formula =LEFT(D3,1) I need to add up all the U1s with S column Ms in S so I get 30.14 The U2s with S column Ms so I get 36.05 and U3s with S column Ms so I get 26.5. The #N/As are not needed. Again, much appreciated if it can be fixed. D E F N S U 112 4444 Montana 8 M 1 112 5555 Farve 8 M 1 122 6666 Aiken 8 M 1 198 7777 Manning 6.14 M 1 212 9999 Young 8 M 2 212 8888 Unitas 8 #N/A 2 212 9991 Starr 10 M 2 212 8884 Elway 10.05 M 2 222 6664 Rice 8 M 2 312 2223 Payton 8 M 3 312 1245 Brown 10.5 M 3 312 1478 Sayers 8 M 3 312 4587 Morino 8 #N/A 3 312 5689 taylor 8 #N/A 3 "PCLIVE" wrote: Looks like C is being treated as text. Try this: (basically, put quotes around the numbers for column C. 1's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="1"),A1:A100) 2's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="2"),A1:A100) 3's =SUMPRODUCT(--(B1:B100="M"),--(C1:C100="3"),A1:A100) HTH, Paul "Steve" wrote in message ... Thanks guys, but I'm not able to get either suggestion to work. I think it may be because Columns B & C are formula references as follows: B = VLOOKUP(E3,'data'!$B$2:$G$50,6,FALSE) and C is =LEFT(D3,1) "Pete_UK" wrote: In column D you can record these values: D1: 1 D2: 2 D3: 3 Then put this formula in E1: =SUMPRODUCT((C$1:C$20=D1)*(B$1:B$20="M")*(A$1:A$20 )) and copy it down into E2:E3. Adjust ranges if you have more than 20 rows of data. Hope this helps. Pete On Mar 11, 8:13 pm, Steve wrote: I need to add the A column if B has M and C has 1, and also for the 2's in C if M & the 3's in C if M. I need 24 for my corresponding 1s 36.05 for my corresponding 2s 42.5 for my corresponding 3s The #N/A s are not needed. I don't think I asked this very well, but I hope it's understandable. Thanks, A B C 8 M 1 8 M 1 8 M 1 6.14 #N/A 1 8 M 2 8 M 2 10 M 2 10.05 M 2 8 #N/A 2 8 M 3 10.5 M 3 8 M 3 8 M 3 8 M 3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summing not summing | Excel Discussion (Misc queries) | |||
| Summing not summing | Excel Worksheet Functions | |||
| PivotTable and summing/not summing | Excel Discussion (Misc queries) | |||
| Summing all BUT one... | Excel Worksheet Functions | |||
| Help with summing | Excel Worksheet Functions | |||
Linear Mode
