True or False

[Krish]

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?

 

[Bruno Campanini]

I have a list in a spreadsheet as follows. I want a formula to fill the
Criteria column. The condition should be
Invoice dollars should be less than $ 200 and
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.
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"
InvoiceCustomerSalesCriteria
W112311CRE100799.15
W112344CRE100185.42
W112345DRW200199.49
W112452HLE300129.95
W112457HLE30041.54

How can I accomplish the multiple conditions in one formula?
----------------------------------------

Name Customers the Customer range
Name Sales the Sales range

=IF(SUMIF(Customers,Customers,Sales)<=200,TRUE,FALSE)

Ciao
Bruno

 

[joeu2004]

How can I accomplish the multiple conditions in one formula?

Yes. But the logic you describe below if unclear.

I want a formula to fill the Criteria column. The
condition should be
a.. Invoice dollars should be less than $ 200 and

Does this mean that if any invoice exceeds $200, you
want FALSE? That seems to be redundant with #c.

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.

Do you mean "at least two" or "exactly two"?

And do you mean "less than or equal to"?
Alternatively, see the 1st question for #c.

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"

Do you mean "greater than or equal to"?
Alternatively, see the 2nd question for #b.
What about the condition when there is a single
invoice (and sum of more than 2 invoices, depending
on your answer to the question for #b above) for a
customer, and it is less than $200? Do you want
TRUE?

Invoice Customer Sales Criteria
W112311 CRE100 799.15
W112344 CRE100 185.42
W112345 DRW200 199.49
W112452 HLE300 129.95
W112457 HLE300 41.54

In the future, it would be ideal if you filled what
you expect in the Criteria column, as an example.

Assume the data above are in Columns A-D.

There are at least two possible solutions, depending
on your answers above.

1. In the simplest form, it seems to me that your
condition reduce down to one: if the sum of all
invoices for a customer is less than or equal to $200,
TRUE; else FALSE. If you agree, put the following
into D2 and copy down through D6 (tested).

=IF(SUMIF($B$2:$B$6,B2,$C$2:$C$6)<=200,TRUE,FALSE)

2. To match the logic of your conditions, in case my
understanding is wrong and to demonstrate the
paradigm for handling "multiple conditions", put
the following into D2 and copy down through D6
(untested).

=IF(B2>200,FALSE,IF(COUNTIF($B$2:$B$6,B2)=1,TRUE,
IF(SUMIF($B$2:$B$6,B2,$C$2:$C$6)>200,FALSE,TRUE))

 

[Ashish Mathur]

Hi,

In the criteria column, enter the following formula
I have worked with the following example. I have entered the data on names
and amounts. True or false is the output.

Ashish 1 True
Ashish 5 True
Mathur 3 True

=IF(SUMIF($B$4:$B$6,B4,$C$4:$C$6)<200,"True","False")

Please adjust the example to yourt needs.

Regards,