|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 21st, 2009, 01:01 PM
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
May 21st, 2009, 01:08 PM
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
May 21st, 2009, 01:08 PM
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
May 21st, 2009, 01:09 PM
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
May 21st, 2009, 01:16 PM
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
May 21st, 2009, 01:23 PM
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
May 21st, 2009, 01:37 PM
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
May 21st, 2009, 01:46 PM
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
May 21st, 2009, 02:08 PM
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
May 21st, 2009, 03:39 PM
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 -
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
