Need formula help

[aparvez007]

Dear Ninja's

I have data AM2:AO78 and I want formula in AG3 sum of all customer for model1.
Please check uploaded file.


Regards,
pAvi

 

[Somendra Misra]

Hi,

Isn't this formula serving your purpose?
=SUMIF($AN$3:$AN$78,$D3,$AO$3:$AO$78)


Regards,

 

[aparvez007]

Thanks SM,

I have to sum only for customer which is in row 2 because AM2:AO78 have customer other than F2:AF2.


Regards,
pAvi

 

[Somendra Misra]

Can you explain one example.

Regards,

 

[aparvez007]

Ok lets say Model 17 in data range AM2:AO78 have customer Cust10 & Cust101 to result should be 4 because Cust101 is not there in range F2:AF2.
Please check uploaded file.


Regards,
pAvi

 

[Somendra Misra]

Try below formula,

=SUMPRODUCT(($AN$3:$AN$78=$D3)*(ISNUMBER(MATCH($AM$3:$AM$78,$F$2:$AF$2,0)))*$AO$3:$AO$78)

Regards,

 

[V_Agarwal]

You may also try this formula in cell AG3.
=SUMPRODUCT(SUMIFS($AO$2:$AO$78,$AN$2:$AN$78,$D3,$AM$2:$AM$78,$F$2:$AF$2))
you may drag this formula down.

 

[Faseeh]

This can also be used:

=SUMPRODUCT((D3&F$2:AF$2=AN$3:AN$78&AM$3:AM$78)*(AO$3:AO$78))

 

[aparvez007]

SM, V_Agarwal & Faseeh Thanks a lot all 3 formula working.

Thanks again.


Regards,
pAvi