Large Based on Criteria

[Guest]

I have a spreadsheet in which data is imported daily. It records
transactions of each client. The general set-up is as follows:

(Customer) (Amount)
A $50.00
B $187.00
B $69.00
C $58.00
C $43.00
C $82.00
D $199.00
D $25.00

My problem is the information isn't static. When I download it, each
customer is never on a specific row or in a specific column. Sometimes
Customer A will purchase only 1 item or even no items, in which they wouldn't
appear on the report. Customer B might go on a shopping spree and purchase
50 items once a year. I've already got a sumif formula in place to tell me
the total purchased by each customer, but I want to show the largest ticket
item for each customer. Is this even possible since my report size and range
change daily?

 

[Guest]

=MAX((A2:A9="C")*(B2:B9))

Enter with Ctrl+Shift+Enter

 

[Guest]

Thanks, that did work!
What is the significance of Ctrl+Shift+Enter?

 

[Guest]

The formula is an "array formula" and this is entered using Ctrl+Shift+Enter

=MAX((A2:A9="C")*(B2:B9))

so it calculates A2*B2, A3*B3 etc

if A2 (etc) ="C" the value is 1, otherwise 0 so results for "An" not equal
to "C" will be 0 and the results for A="C" will be the corresponding values
in B (58,43 and 82).

so MAX=82

HTH

 

[Guest]

You are wonderful. Thanks again,
Peanut

The formula is an "array formula" and this is entered using Ctrl+Shift+Enter

=MAX((A2:A9="C")*(B2:B9))

so it calculates A2*B2, A3*B3 etc

if A2 (etc) ="C" the value is 1, otherwise 0 so results for "An" not equal
to "C" will be 0 and the results for A="C" will be the corresponding values
in B (58,43 and 82).

so MAX=82

HTH