I haven't been able to find an exact answer to this question, so I'm hoping someone out there knows the answer. Basically, I have a table of imported regional sales data by date in an Excel workbook from which I am then trying to sum values for certain products sold on that date.
I've given names to the columns in the imported data to make it easier -- 'date', 'office', 'product', 'qty', 'avgPrice', etc....
Imported Table
DATE PRODUCT OFFICE QTY PRICE
3/4/2020 CAP-A ILLICH 5 $4.19
3/1/2020 CAP-A TOPEKA 3 $4.26
3/3/2020 RAD-C ILLICH 7 $7.19
3/4/2020 RAD-B ILLICH 2 $7.02
3/4/2020 CAP-A TOPEKA 10 $4.17
3/1/2020 CAP-A TOPEKA 7 $4.23
3/3/2020 RAD-A ILLICH 9 $6.98
3/1/2020 CAP-A TOPEKA 2 $4.21
Desired output
DATE PRODUCT Total_QTY
3/1/2020 CAP-A 12
3/2/2020 CAP-A 0
3/3/2020 CAP-A 0
3/4/2020 CAP-A 15
The formula I'm using is: =SUMIFS(qty,date,"=[@DATE]",product,"=[@PRODUCT]")
The problem is, this will always return a sum of "0" for that entry incorrectly. However, if I make the change where instead of using "=[@DATE]" is manually input the date as "=3/1/2020", and replace "=[@PRODUCT]" with "=CAP-A", then I get the correct results.
Of course, I can go through and input the date and product manually, but I have hundreds of dates right now and several products, and this will likely turn into thousands over time, and I may have to import new figures using the same framework but with historical records, so different dates and products. I don't want to have to type out these comparisons manually every time
Is there something simple I'm overlooking here, or a way of comparing the the two fields I'm not aware of?
