CODEWITHSUNDEEP
excelexcel-formulavlookuplookup-tables
Ann
Ann

Reputation: 328

Lookup based on two columns and return sum in the new table

I have a Excel file with 100,000 rows , first 10 rows:

    Date       Product   Sales_in_Units  Sales USD  
    1/1/2016    BM-D4     928            4,649    
    1/1/2016    BM-XN     266            685,740      
    1/2/2016    BM-B10    910            1,144    
    1/2/2016    PIB-H20   746            2,580,000    
    1/2/2016    PIB-H20   143            3,768,734   
    1/3/2016    VQR-GG2   269            570,794      
    1/3/2016    WS2-B18   106            432,400      
    1/4/2016    VQR-GG2   345            145,692      
    1/4/2016    BM-D4     234            747,541      
    1/5/2016    VQR-GG2   456            1,218    
    1/6/2016    PIB-H20   14             260,000 
...

I would like to be able to retrieve the Sum for specific Date and Product and fill the new table like (below). I was thinking this might be done with LOOKUP. Any help would be greatly appreciated.

Date        Product         Total_Sales_in_Units    Total_Sales USD
1/1/2016    BM-XN           
1/3/2016    VQR-GG2         
1/4/2016    VQR-GG2         
1/6/2016    PIB-H20         
...

Upvotes: 0

Views: 65

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

With data in columns A through D, in E1 enter the desired date and in F1 enter the desired product.

Then in G1 enter:

=SUMPRODUCT(--(A2:A100=E1)*(B2:B100=F1)*(C2:C100))

and in H1 enter:

=SUMPRODUCT(--(A2:A100=E1)*(B2:B100=F1)*(D2:D100))

enter image description here

Then fill down columns E through H with all the desired combinations you need.

Upvotes: 2

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5205

Use SUMIFS, e.g. for sales in units:

= SUMIFS($C:$C,$A:$A,<date>,$B:$B,<product>)

and for sales USD:

= SUMIFS($D:$D,$A:$A,<date>,$B:$B,<product>)

Just replace <date> and <product> in formula above with whichever ones you want to look up.

Upvotes: 3

Related Questions