• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

bhasoriya

Member
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
 
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 ?
 
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)))
 
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
 
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
 
Back
Top