Multiple match and multiply

[Thomas Kuriakose]

Respected Sirs,

We have a file where there are categories and names which need to be matched and counted and if the counts are within a range it should be multiplied with the respective rates -

Category in A2:A63 and more
Name in B2:B63 and more

For a specific name the categories need to be counted -

If for Aman the count of categories of 200 is between 1-15 multiply price (Column C)by I4 = 90
If for Aman the count of categories of 200 is greater than 15 multiply price (Column C) (by J4 = 90
If for Raj the count of categories of 300 is between 1-15 multiply price (Column C) ( by I3 = 100
If for Aman the count of categories of 300 is greater than 15 multiply price (Column C) by J3 = 150

Kindly find attached the file for your reference.

Thank you very much,

with regards,
thomas

 

[bosco_yip]

Try,

In E2, copied down :

=C2*INDEX({100,150;90,100},(A2=200)+1,(C2>15)+1)

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided.

Kindy check the values for E2:E7, the values are not multiplying by 100 in this case.

Sir, also, if there are more categories to be added, can we use the same solution.

Thank you very much,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

I missed to mention. The count of Category (200) in A2 to A17 for name Aman is 16 and this is greater than 15, so it should multiply by 100 (J4).

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided for this query.

This worked perfectly.

Much appreciated,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

One small issue, it is not taking both values for less than equal to 15 and greater than 15. In some cases it is taking one value.

Kindly find attached the file for your reference.

Thank you very much,

with regards,
thomas

 

[rahulshewale1]

hii,

try below formula..using helper column

pfa sheet light blue highlighted ..

=IF(AND(F2<15,A2=200),C2*90,IF(AND(F2>15,A2=200),C2*100,IF(AND(F2<15,A2=300),C2*100,IF(AND(F2>15,A2=300),C2*150))))


Thanks
rahul shewale

 

[Thomas Kuriakose]

Respected Sir,

Thank you for this solution, but we need to match category and name for each row. The actual data has 38k rows.

The count should be for names with respect to the category and if both match then count the number of instances to be verified with <=15 or >15.

Thank you very much once again,

with regards,
thomas

 

[rahulshewale1]

Hii
please check.

=IF(AND(COUNTIFS($A$2:$A$63,A2,$B$2:$B$63,B2)<15,A2=200),C2*90,IF(AND(COUNTIFS($A$2:$A$63,A2,$B$2:$B$63,B2)>15,A2=200),C2*100,IF(AND(COUNTIFS($A$2:$A$63,A2,$B$2:$B$63,B2)<15,A2=300),C2*100,IF(AND(COUNTIFS($A$2:$A$63,A2,$B$2:$B$63,B2)>15,A2=300),C2*150))))

regard
Rahul shewale

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the revised formula.

I tried deleting some rows in the first batch (A2:A13) from A2:A17 and found the values are not getting updated.

Kindly check the attached.

Thank you very much,

with regards,
thomas

 

[bosco_yip]

1] Another option with helper column way.

2] See attached file.

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the revised solution.

Much appreciated,

with regards,
thomas