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

2
votes

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.

3
google-sheetssumlogicvlookupcountif

3 Answers

1
votes

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

2
votes

The simpliest solution is to use a pivot table, see inside your spreadsheet. If you want the total inside the original data, and take advantage of this simple solution, you can then recall the total by GETPIVOTDATA

1
votes

In cell K4 I entered

={"Result Col"; ArrayFormula(if(len(G5:G),if(match(G5:G&H5:H,G5:G&H5:H,0)+4=row(G5:G),sumif(G5:G&H5:H,G5:G&H5:H,I5:I),),))}

enter image description here

I used the same formula (with different range) in E1 (for what I assume to be your 'real' data).

See if that helps?