Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUE/FALSE
Back in Novemeber I posted this question and I got the reply, which helped
meet my need. But after my computer hard drive crash, I lost the spreadsheet. Can I request one more time for the answer? Also in the list if I introduce a field for date of the Invoice, what would be the formula to distinguish, the date? I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUE/FALSE
If Invoices ar in column A, Customers in B, sales in C and criteria in
D then this formula should work: =SUMIF(B:B;B2;C:C)<200 It sums all the sales where customer equals B2, and then checks if that value is less than 200, if so it returns false. Per Erik On Tue, 3 Jan 2006 14:16:44 -0500, "Krish" wrote: Back in Novemeber I posted this question and I got the reply, which helped meet my need. But after my computer hard drive crash, I lost the spreadsheet. Can I request one more time for the answer? Also in the list if I introduce a field for date of the Invoice, what would be the formula to distinguish, the date? I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUE/FALSE
Thank you very much for the tips. Could you please answer my second part of
the question. If I have the data for the entire month, how will I be able to identify for each day in the month 'True/False"? "Per Erik Midtrød" wrote in message ... If Invoices ar in column A, Customers in B, sales in C and criteria in D then this formula should work: =SUMIF(B:B;B2;C:C)<200 It sums all the sales where customer equals B2, and then checks if that value is less than 200, if so it returns false. Per Erik On Tue, 3 Jan 2006 14:16:44 -0500, "Krish" wrote: Back in Novemeber I posted this question and I got the reply, which helped meet my need. But after my computer hard drive crash, I lost the spreadsheet. Can I request one more time for the answer? Also in the list if I introduce a field for date of the Invoice, what would be the formula to distinguish, the date? I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUE/FALSE
You're welcome.
The part with the date field is slightly more complicated, since Sumif can only have on criteria. This function does what you are looking for if the dates are in column D =SUMPRODUCT((B$2:B$6=B2)*(D$2:D$6=D2)*(C$2:C$6))<2 00 Please notice the use of absolute references. Per Erik On Tue, 3 Jan 2006 15:53:57 -0500, "Krish" wrote: Thank you very much for the tips. Could you please answer my second part of the question. If I have the data for the entire month, how will I be able to identify for each day in the month 'True/False"? "Per Erik Midtrød" wrote in message .. . If Invoices ar in column A, Customers in B, sales in C and criteria in D then this formula should work: =SUMIF(B:B;B2;C:C)<200 It sums all the sales where customer equals B2, and then checks if that value is less than 200, if so it returns false. Per Erik On Tue, 3 Jan 2006 14:16:44 -0500, "Krish" wrote: Back in Novemeber I posted this question and I got the reply, which helped meet my need. But after my computer hard drive crash, I lost the spreadsheet. Can I request one more time for the answer? Also in the list if I introduce a field for date of the Invoice, what would be the formula to distinguish, the date? I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUE/FALSE
Per:
You are awesome. If I want to count the number of Invoices for a month, counting multiple Invoices in a day for the same customer as one, what function should I use? Multiple Invoices are delivered using one single shipping document and therefore, there is only one delivery. My goal is to determine the average sales dollars per shipping. "Per Erik Midtrød" wrote in message ... You're welcome. The part with the date field is slightly more complicated, since Sumif can only have on criteria. This function does what you are looking for if the dates are in column D =SUMPRODUCT((B$2:B$6=B2)*(D$2:D$6=D2)*(C$2:C$6))<2 00 Please notice the use of absolute references. Per Erik On Tue, 3 Jan 2006 15:53:57 -0500, "Krish" wrote: Thank you very much for the tips. Could you please answer my second part of the question. If I have the data for the entire month, how will I be able to identify for each day in the month 'True/False"? "Per Erik Midtrød" wrote in message .. . If Invoices ar in column A, Customers in B, sales in C and criteria in D then this formula should work: =SUMIF(B:B;B2;C:C)<200 It sums all the sales where customer equals B2, and then checks if that value is less than 200, if so it returns false. Per Erik On Tue, 3 Jan 2006 14:16:44 -0500, "Krish" wrote: Back in Novemeber I posted this question and I got the reply, which helped meet my need. But after my computer hard drive crash, I lost the spreadsheet. Can I request one more time for the answer? Also in the list if I introduce a field for date of the Invoice, what would be the formula to distinguish, the date? I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRUE/FALSE
With the dates in Column D you could put this formula in row 2 in any
column and copy down. SUMPRODUCT((B$2:B2=B2)*(D$2:D2=D2)) Then you could use SumProduct to count all the ones within the month you are checking. Perhaps like this: SUMPRODUCT((MONTH(D2:D6)=1)*(F2:F6=1)) If you put the first formula in colum F and want to check january that is. Per Erik On Tue, 3 Jan 2006 21:19:53 -0500, "Krish" wrote: Per: You are awesome. If I want to count the number of Invoices for a month, counting multiple Invoices in a day for the same customer as one, what function should I use? Multiple Invoices are delivered using one single shipping document and therefore, there is only one delivery. My goal is to determine the average sales dollars per shipping. "Per Erik Midtrød" wrote in message .. . You're welcome. The part with the date field is slightly more complicated, since Sumif can only have on criteria. This function does what you are looking for if the dates are in column D =SUMPRODUCT((B$2:B$6=B2)*(D$2:D$6=D2)*(C$2:C$6))<2 00 Please notice the use of absolute references. Per Erik On Tue, 3 Jan 2006 15:53:57 -0500, "Krish" wrote: Thank you very much for the tips. Could you please answer my second part of the question. If I have the data for the entire month, how will I be able to identify for each day in the month 'True/False"? "Per Erik Midtrød" wrote in message .. . If Invoices ar in column A, Customers in B, sales in C and criteria in D then this formula should work: =SUMIF(B:B;B2;C:C)<200 It sums all the sales where customer equals B2, and then checks if that value is less than 200, if so it returns false. Per Erik On Tue, 3 Jan 2006 14:16:44 -0500, "Krish" wrote: Back in Novemeber I posted this question and I got the reply, which helped meet my need. But after my computer hard drive crash, I lost the spreadsheet. Can I request one more time for the answer? Also in the list if I introduce a field for date of the Invoice, what would be the formula to distinguish, the date? I have a list in a spreadsheet as follows. I want a formula to fill the Criteria column. The condition should be a.. Invoice dollars should be less than $ 200 and b.. if there are two Invoices for the same customer and if the sum of all Invoices is still less than $ 200, it should reflect as "TRUE" for both Invoices. c.. If the dollar amount of an Individual Invoice or sum of all Invoices for a customer is greater than $ 200, it should reflect as "FALSE" Invoice Customer Sales Criteria W112311 CRE100 799.15 W112344 CRE100 185.42 W112345 DRW200 199.49 W112452 HLE300 129.95 W112457 HLE300 41.54 How can I accomplish the multiple conditions in one formula? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indexing/Matching True/False results | Excel Worksheet Functions | |||
if test using true/false or yes/no | Excel Discussion (Misc queries) | |||
Displaying data based on a TRUE/FALSE value in a cell | Excel Discussion (Misc queries) | |||
Want to change the color of a true/false logical statement with i. | Excel Worksheet Functions | |||
replacing TRUE/FALSE | New Users to Excel |