Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell on either side is something count in sum
Hi,
I'm looking at an array to make a sum something like this, but it's not working. Can I get some help? =IF($G$5:$G$67<"x",SUM(IF($C$5:$C$67="Company",($ D$5:$D$67))),0) Column D is the amount to sum, but Column G cannot have an 'x' beside the amount in column D to count amount in the sum, and Column C has to match 'Company' beside the amount in column D to count the amount in the sum as well. Column D is the invoice amount Column G is x'd when it's paid Column C is the company name E77 is the formula showing how much company has outstanding. I get 0 & it should be $18,850.00. Thanks, Michele |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell on either side is something count in sum
Hi, Michelle,
This is a straightforward syntax matter. In the end, you want to perform the SUM, so the SUM should be the outsidemost function, with the nested IF statements inside, like this: =SUM(IF(condition,true,if(condition,true,false),fa lse)) Notice the symmetry if you read it from the center out? It has been about 25 minutes since you posted, so you might have noticed this yourself by now. Regards, Marty S. "mjones" wrote: Hi, I'm looking at an array to make a sum something like this, but it's not working. Can I get some help? =IF($G$5:$G$67<"x",SUM(IF($C$5:$C$67="Company",($ D$5:$D$67))),0) Column D is the amount to sum, but Column G cannot have an 'x' beside the amount in column D to count amount in the sum, and Column C has to match 'Company' beside the amount in column D to count the amount in the sum as well. Column D is the invoice amount Column G is x'd when it's paid Column C is the company name E77 is the formula showing how much company has outstanding. I get 0 & it should be $18,850.00. Thanks, Michele |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell on either side is something count in sum
=IF($G$5:$G$67<"x",SUM(IF($C$5:$C$67="Company",($ D$5:$D$67))),0)
One way is to use SUMPRODUCT Try, normal ENTER will do: =SUMPRODUCT(($G$5:$G$67<"x")*($C$5:$C$67="Company "),$D$5:$D$67) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mjones" wrote: Hi, I'm looking at an array to make a sum something like this, but it's not working. Can I get some help? =IF($G$5:$G$67<"x",SUM(IF($C$5:$C$67="Company",($ D$5:$D$67))),0) Column D is the amount to sum, but Column G cannot have an 'x' beside the amount in column D to count amount in the sum, and Column C has to match 'Company' beside the amount in column D to count the amount in the sum as well. Column D is the invoice amount Column G is x'd when it's paid Column C is the company name E77 is the formula showing how much company has outstanding. I get 0 & it should be $18,850.00. Thanks, Michele |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell on either side is something count in sum
Thanks! Both options work.
Max wrote: =IF($G$5:$G$67<"x",SUM(IF($C$5:$C$67="Company",($ D$5:$D$67))),0) One way is to use SUMPRODUCT Try, normal ENTER will do: =SUMPRODUCT(($G$5:$G$67<"x")*($C$5:$C$67="Company "),$D$5:$D$67) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mjones" wrote: Hi, I'm looking at an array to make a sum something like this, but it's not working. Can I get some help? =IF($G$5:$G$67<"x",SUM(IF($C$5:$C$67="Company",($ D$5:$D$67))),0) Column D is the amount to sum, but Column G cannot have an 'x' beside the amount in column D to count amount in the sum, and Column C has to match 'Company' beside the amount in column D to count the amount in the sum as well. Column D is the invoice amount Column G is x'd when it's paid Column C is the company name E77 is the formula showing how much company has outstanding. I get 0 & it should be $18,850.00. Thanks, Michele |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell on either side is something count in sum
Glad it did!
Thanks for feedback -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- mjones wrote: Thanks! Both options work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Cell References | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
count a number range and a letter in a cell | Excel Worksheet Functions | |||
Cell Count | New Users to Excel |