Need your help for SUMIF, LEFT, RIGHT with Name criteria [SOLVED]

[bhasoriya]

Stuck with too many formula. Not get an idea how the get the result.


http://rapidshare.com/files/2467620047/SUMIF%20LEFT%20RIGHT%20with%20Name%20criteria.xlsx

 

[Hui]

Bhasoriya


A few questions


1. You want to sum task 1 & 2 when the file name matches your criteria

Are there only 2 or 3 rows per date, or many records per date?


2. I assume you want to do this for each date out of many dates ?

 

[bhasoriya]

Hello Hui,


Que 1. => yes.

Que 2. => 2 or 3 rows per date.

Que 3. => yes.


Thanks for Responding.

 

[Hui]

F3: =IF(NOT(OR(RIGHT(C3,5)="N.jpg",RIGHT(C3,5)="T.jpg")),D3+E3,SUMPRODUCT(($B$2:$B$30=B3)*(LEFT($C$2:$C$30,15)=LEFT(C3,15))*((RIGHT($C$2:$C$30,5)="T.jpg")+(RIGHT($C$2:$C$30,5)="N.jpg"))*($D$2:$E$30)))

 

[bhasoriya]

Thanks Hui.. It is working fine. but little bit problem,


When there is file name contain N.jpg then the value should be in F row (F2) blank or zero. Cause it is added in T.jpg

 

[bhasoriya]

Can anybody help me this..

extreme need for your help.

 

[NARAYANK991]

Hi Bhasoriya ,


Try this in F2 :


=IF(RIGHT(C2,5)="N.jpg","",IF(RIGHT(C2,5)<>"T.jpg",D2+E2,SUMPRODUCT(($B$2:$B$30=B2)*(LEFT($C$2:$C$30,15)=LEFT(C2,15))*((RIGHT($C$2:$C$30,5)="T.jpg")+(RIGHT($C$2:$C$30,5)="N.jpg"))*($D$2:$E$30))))


Narayan

 

[bhasoriya]

Thanks Narayan, it is working perfectly..

You always help me in need.


Salute you.

Thank you..