| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum if and with multiple criteria
Here's my data
Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
This should work as long as your dont have any error within C6:C488 range...
=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488*0.02) -- If this post helps click Yes --------------- Jacob Skaria "Micki" wrote: Here's my data Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
Hi,
Your formula works for me, if you are importing data in that columns do Text to columns for each column "Micki" wrote: Here's my data Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
Check col C's values. There's probably some text/error values within. Clear
it up and it should work fine. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Micki" wrote: Here's my data Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
I didn't get an error with yours but maybe? Are your numbers numbers
=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488)*0.02 -- Don Guillett Microsoft MVP Excel SalesAid Software "Micki" wrote in message ... Here's my data Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
does
=SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488))*.02 help? On 21 Maj, 14:01, Micki wrote: Here's my data Column A * * *Column B * * Column C H * * * * * * * * * * B1 * * * * * * *10 I * * * * * * * * * * *B2 * * * * * * *12 J * * * * * * * * * * *B2 * * * * * * *15 K * * * * * * * * * * B3 * * * * * * * 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
Some cells contained a value of "", I made them zeros and it worked. Thanks
everyone. "Don Guillett" wrote: I didn't get an error with yours but maybe? Are your numbers numbers =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488)*0.02 -- Don Guillett Microsoft MVP Excel SalesAid Software "Micki" wrote in message ... Here's my data Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
Fine. But Micki this should handle blanks as well...
If this post helps click Yes --------------- Jacob Skaria "Micki" wrote: Some cells contained a value of "", I made them zeros and it worked. Thanks everyone. "Don Guillett" wrote: I didn't get an error with yours but maybe? Are your numbers numbers =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488)*0.02 -- Don Guillett Microsoft MVP Excel SalesAid Software "Micki" wrote in message ... Here's my data Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02)) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
=SUMPRODUCT((--($A$6:$A$488="H"))*(--($B$6:$B$488="B1")*(LEN(C$6:C$488)
0),C$6:C$488) should handle those "" On 21 Maj, 14:37, Micki wrote: Some cells contained a value of "", I made them zeros and it worked. Thanks everyone. "Don Guillett" wrote: I didn't get an error with yours but maybe? Are your numbers numbers =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488)*0..02 -- Don Guillett Microsoft MVP Excel SalesAid Software "Micki" wrote in message ... Here's my data Column A Â* Â* Â*Column B Â* Â* Column C H Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* B1 Â* Â* Â* Â* Â* Â* Â*10 I Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*B2 Â* Â* Â* Â* Â* Â* Â*12 J Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*B2 Â* Â* Â* Â* Â* Â* Â*15 K Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* B3 Â* Â* Â* Â* Â* Â* Â* 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02))- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if and with multiple criteria
.... and of course you don't need the double unary minus -- if you have
multiplied with *. --A*--B is the same as =A*B -- David Biddulph "Jarek Kujawa" wrote in message ... =SUMPRODUCT((--($A$6:$A$488="H"))*(--($B$6:$B$488="B1")*(LEN(C$6:C$488) 0),C$6:C$488) should handle those "" On 21 Maj, 14:37, Micki wrote: Some cells contained a value of "", I made them zeros and it worked. Thanks everyone. "Don Guillett" wrote: I didn't get an error with yours but maybe? Are your numbers numbers =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),C$6:C$488)*0.02 -- Don Guillett Microsoft MVP Excel SalesAid Software "Micki" wrote in message ... Here's my data Column A Column B Column C H B1 10 I B2 12 J B2 15 K B3 14 I want to sum Column C and multiply times .02 if the values in Column A=H, and the values in column B=B1. This formula works until I add the multiplication factor, then I get a return of #VALUE =SUMPRODUCT(--($A$6:$A$488="H"),--($B$6:$B$488="B1"),((C$6:C$488)*.02))- Ukryj cytowany tekst - - Pokaz cytowany tekst - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
| match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
