yogi_Compute Number Of Tickets Sold In Specified WeekNumber To A Set Of Specified Customers

                 Google Spreadsheet   Post  #1828

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-10-2014

post by  AMOC:

(https://productforums.google.com/forum/#!mydiscussions/docs/-mwPqfHoC8I)

Sumifs with array

Hi all

I'm having a terrible time getting a formula that works in Excel to work in Google Sheets.


I have a data table that includes headings for Customer, Week of Year, Day of Week, and Ticket Count.

I have a lookup table that includes a ranked list of the top 15 customers we have.

I'm trying to design a sumifs formula that will sum up the ticket count if the week = 40 AND the customer is in the top 15 customer list.

Here is the formula in Excel, which is confirmed working (without using the top15 customer lookup table):

=SUM(SUMIFS(TicketCount,Weekname,40,Customer,{"=CUSTOMER1","=CUSTOMER2","=CUSTOMER3",..."=CUSTOMER15"}))


i would REALLY love it if i could bring this down in to google sheets.  I've tried it multiple times but all it ever does is count up the tickets for the first customer alone.

Eventually, i'd like to be able to use a named range in place of the array so that if the list of top15 customers changes i don't have to manually update all of the formulas.


halp?

---------------------------------------------------------------------------------------------------------------------------------

In the following is a solution using the QUERY function