Large Based on Criteria
 

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?

Large Based on Criteria
 

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

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

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?

Large Based on Criteria
 

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



"Toppers" wrote:

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

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

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?

Large Based on Criteria
 

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




"Peanut" wrote:

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



"Toppers" wrote:

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

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

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?

Large Based on Criteria
 

You are wonderful. Thanks again,
Peanut



"Toppers" wrote:

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




"Peanut" wrote:

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



"Toppers" wrote:

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

Enter with Ctrl+Shift+Enter

"Peanut" wrote:

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?