Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If with two ranges and condition
Hy
I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1" With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100) Any help please |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If with two ranges and condition
One way:
=SUMPRODUCT(--(B1:B100=DATE(2006,1,1)),--(C1:C100=1),A1:A100) For an explanation of the "--" usage, see http://www.mcgimpsey.com/excel/doubleneg.html In article , Nuno Jácome wrote: Hy I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1" With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100) Any help please |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If with two ranges and condition
Hi !
It don't work ! I know that exist a way to do it ! summing with to criteria: Sum A1:A100 If B1:B100 = "text" and C1:C100 = "text1" Thanks any way "JE McGimpsey" escreveu: One way: =SUMPRODUCT(--(B1:B100=DATE(2006,1,1)),--(C1:C100=1),A1:A100) For an explanation of the "--" usage, see http://www.mcgimpsey.com/excel/doubleneg.html In article , Nuno Jácome wrote: Hy I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1" With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100) Any help please |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If with two ranges and condition
If both criteria fields are text:
=SUMPRODUCT(--(B1:B100="01-01-2006");--(C1:C100="1");A1:A100) In article , Nuno Jácome wrote: Hi ! It don't work ! I know that exist a way to do it ! summing with to criteria: Sum A1:A100 If B1:B100 = "text" and C1:C100 = "text1" Thanks any way "JE McGimpsey" escreveu: One way: =SUMPRODUCT(--(B1:B100=DATE(2006,1,1)),--(C1:C100=1),A1:A100) For an explanation of the "--" usage, see http://www.mcgimpsey.com/excel/doubleneg.html In article , Nuno Jácome wrote: Hy I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1" With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100) Any help please |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If with two ranges and condition
Hi again,
What I'm doing wrong ? =SOMA.SE(Plano!$C$9:Plano!$C$724;"01-12-2004";Plano!$L$9:Plano!$L$724) This is OK. when I try your sugestion to put one more criteria it don't work the cell stay blank with the formula inside (no error) =SOMARPRODUTO(--(Plano!$C$9:Plano!$C$724="01-12-2004");--(Plano!$P$9:Plano!$P$724="1");Plano!$L$9:Plano!$L$ 724) Thanks in advance "JE McGimpsey" escreveu: If both criteria fields are text: =SUMPRODUCT(--(B1:B100="01-01-2006");--(C1:C100="1");A1:A100) In article , Nuno Jácome wrote: Hi ! It don't work ! I know that exist a way to do it ! summing with to criteria: Sum A1:A100 If B1:B100 = "text" and C1:C100 = "text1" Thanks any way "JE McGimpsey" escreveu: One way: =SUMPRODUCT(--(B1:B100=DATE(2006,1,1)),--(C1:C100=1),A1:A100) For an explanation of the "--" usage, see http://www.mcgimpsey.com/excel/doubleneg.html In article , Nuno Jácome wrote: Hy I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1" With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100) Any help please |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If with two ranges and condition
Thanks, with your indications I could solve the problem like this:
=SOMARPRODUTO(--(Plano!$C$9:Plano!$C$724=DATA(2004;12;1));--(Plano!$P$9:Plano!$P$724=1);Plano!$L$9:Plano!$L$72 4) "JE McGimpsey" escreveu: If both criteria fields are text: =SUMPRODUCT(--(B1:B100="01-01-2006");--(C1:C100="1");A1:A100) In article , Nuno Jácome wrote: Hi ! It don't work ! I know that exist a way to do it ! summing with to criteria: Sum A1:A100 If B1:B100 = "text" and C1:C100 = "text1" Thanks any way "JE McGimpsey" escreveu: One way: =SUMPRODUCT(--(B1:B100=DATE(2006,1,1)),--(C1:C100=1),A1:A100) For an explanation of the "--" usage, see http://www.mcgimpsey.com/excel/doubleneg.html In article , Nuno Jácome wrote: Hy I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1" With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100) Any help please |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum If with two ranges and condition
Thank you JE. This worked great!
"Nuno Jácome" wrote: Thanks, with your indications I could solve the problem like this: =SOMARPRODUTO(--(Plano!$C$9:Plano!$C$724=DATA(2004;12;1));--(Plano!$P$9:Plano!$P$724=1);Plano!$L$9:Plano!$L$72 4) "JE McGimpsey" escreveu: If both criteria fields are text: =SUMPRODUCT(--(B1:B100="01-01-2006");--(C1:C100="1");A1:A100) In article , Nuno Jácome wrote: Hi ! It don't work ! I know that exist a way to do it ! summing with to criteria: Sum A1:A100 If B1:B100 = "text" and C1:C100 = "text1" Thanks any way "JE McGimpsey" escreveu: One way: =SUMPRODUCT(--(B1:B100=DATE(2006,1,1)),--(C1:C100=1),A1:A100) For an explanation of the "--" usage, see http://www.mcgimpsey.com/excel/doubleneg.html In article , Nuno Jácome wrote: Hy I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1" With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100) Any help please |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you count data that matches more than one condition? | Excel Worksheet Functions | |||
multiple ranges on Vlookup | Excel Discussion (Misc queries) | |||
Average Calculation | Excel Worksheet Functions | |||
Counting Across Multiple Ranges, Based on Condition | Excel Worksheet Functions | |||
how do i plot this kind of data using excel | Excel Discussion (Misc queries) |