TRUE/FALSE

[Krish]

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.

 

[Per Erik Midtrød]

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

 

[Krish]

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]

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$6=D2)*(C$2:C$6))<200

Please notice the use of absolute references.

Per Erik

 

[Krish]

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]

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$22=D2))


Then you could use SumProduct to count all the ones within the month
you are checking.

Perhaps like this:
SUMPRODUCT((MONTH(D26)=1)*(F2:F6=1))

If you put the first formula in colum F and want to check january that
is.


Per Erik