Identify two different items based on condition

[Junarkar]

Hi guys,

In my master file I have invoices. Some invoices will have multiple products or services. If any particular brand has a warranty included in the same invoice, then I need to get the total qty. of warranty sold for that brand.

sample data attached.

Request your thoughts and insights.


Example;
Mobile department;
Samsung - Qty. sold - 4, Warranty sold - 1
Apple - Qty. sold - 10, Warranty sold - 3

Air conditioner;
Samsung - Qty. sold - 15, Warranty sold - 5
LG - Qty. sold - 10, Warranty sold - 0

 

[ETAF]

i have filtered by
samsung - brand
and
Air condition - group
and can only see 4 rows with a qty 1

so how do you identify warranty

then I need to get the total qty. of warranty sold for that brand.

can you give some expected results on the sheet

also what version of excel are you using

 

[bosco_yip]

Try to set up output table as per below

In L2, formula :

=SUMPRODUCT((D$2:D$999=J3)*(F$2:F$999=K3)*ISNUMBER(SEARCH("yr",G$2:G$999)),H$2:H$999)