Excel alternative for Google Sheets' QUERY function?

Other Questions Google Sheets
1

Hey Excel wizards! I’m stuck trying to mimic a Google Sheets formula in Excel. I’ve got a list of customers with repeat purchases. I need to find out:

  1. How many times each customer bought something
  2. Their cheapest purchase
  3. Their total spent
  4. Their average purchase price

In Google Sheets, I use this nifty QUERY function:

=QUERY(Data!A2:E,"select A, sum(E), min(D), sum(D), avg(D) group by A label A 'Email', sum(E) 'Length', min(D) 'Lowest Price', sum(D) 'Total', avg(D) 'Average'",0)

But Excel doesn’t have QUERY. Any ideas how to do this without VBA? If VBA is the only option, I guess I’ll have to use it.

My data looks like this:
Email, Name, Date, Price, Quantity

And I want the output to show:
Email, Purchase Count, Lowest Price, Total Spent, Average Price

Thanks for any help!

2

Excel’s PivotTable feature can handle this without resorting to VBA. Here’s a concise explanation: First, select your data range and navigate to Insert > PivotTable. In the PivotTable Fields pane, position ‘Email’ in the Rows area and ‘Quantity’ in the Values area to count purchases. Then, add ‘Price’ to the Values area three times. For each ‘Price’ field, modify the summary method to show the minimum, sum, and average respectively, and rename these fields to reflect Lowest Price, Total Spent, and Average Price. This configuration replicates the QUERY function’s output without using VBA.

3

hey there! have u tried power query in excel? it’s pretty awesome for this kinda stuff. u can import ur data, group by email, then add custom columns for each calculation. it’s sorta like QUERY but more visual. might take a bit to learn but way easier than vba imho. good luck!

4

As someone who’s worked extensively with both Excel and Google Sheets, I can say that while Excel lacks a direct QUERY equivalent, you can achieve similar results using a combination of functions. Here’s what I’d suggest:

First, create a helper column to count unique occurrences of each email. Use this formula:

=IF(COUNTIF($A$2:A2,A2)>1,0,COUNTIF(A:A,A2)

Then, use SUMIFS, MINIFS, and AVERAGEIFS functions to calculate the required metrics. For example:

=SUMIFS(E:E,A:A,A2) for purchase count
=MINIFS(D:D,A:A,A2) for lowest price
=SUMIFS(D:D,A:A,A2) for total spent
=AVERAGEIFS(D:D,A:A,A2) for average price

Combine these with FILTER or INDEX/MATCH to get your final output. It’s not as elegant as QUERY, but it gets the job done without VBA.