Search code examples
google-sheetssumlogicvlookupcountif

I want to sum value of of third column based on other two columns value in google sheets and col1 and col2 contains multiple duplicate values


https://docs.google.com/spreadsheets/d/1_MeiySJHI8OD84BPDOj_z57My-TXbs2ey4AOkQP3zug/edit?usp=sharing

I am sharing the link of sheet on which I want solution on that sheet also I have tried to explain you the question.

I have three columns I want sum of third col3 value based on first two column value

  • if col1 = 2 and col2 = 40; Then I should get the sum of all value in col3 in which col1 = 2 and col2 = 40

  • if the col1 contains value of 2 and col2 contains 40 than sum of col3 value only those col3 value in which col1=2 and col2=40

so if any of the column value changes than sum of col3 also sum according to that.


Solution

  • use:

    ={"result"; INDEX(IF(COUNTIFS(A2:A&" "&B2:B, A2:A&" "&B2:B, ROW(A2:A), "<="&ROW(A2:A))=1, 
     IFNA(VLOOKUP(A2:A&" "&B2:B, 
     QUERY({A2:A&" "&B2:B, C2:C}, 
     "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, 0)), ))}
    

    enter image description here