Sum value col3 based on value of col1 and col2 for Google Sheets

new1

New Member
Joined
Aug 20, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
I have three columns in which I want sum value of col3 based on col1 and col2 value
if col1 = 1 and col2 = 40
then sum all value of col3
but if col1 = 1 and col2 = 20 or
col1 = 2 and col2 = 40
then sum col3 value according to the col1 and col2 value
for this purpose I am sharing the link of google sheet with dummy data set please check and give me your suggestion or help.
Thank you.Shared google sheet
 
Last edited by a moderator:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Is this what you are asking for ??

TEST FILE.xlsm
ABCD
1col 1col 2col 3
21404001600
3140400 
4140400 
5140400 
6120200400
7120200 
82404001200
9240400 
10240400 
11225250250
Sheet6
Cell Formulas
RangeFormula
D2:D11D2=IF(AND(A2=A1,B2=B1),"",IF(AND(A2=1,B2=40),SUMIFS($C$2:$C$11,$A$2:$A$11,1,$B$2:$B$11,40),IF(AND(A2=1,B2=20),SUMIFS($C$2:$C$11,$A$2:$A$11,1,$B$2:$B$11,20),IF(AND(A2=2,B2=40),SUMIFS($C$2:$C$11,$A$2:$A$11,2,$B$2:$B$11,40),C2))))
 
Upvote 0
Is this what you are asking for ??

TEST FILE.xlsm
ABCD
1col 1col 2col 3
21404001600
3140400 
4140400 
5140400 
6120200400
7120200 
82404001200
9240400 
10240400 
11225250250
Sheet6
Cell Formulas
RangeFormula
D2:D11D2=IF(AND(A2=A1,B2=B1),"",IF(AND(A2=1,B2=40),SUMIFS($C$2:$C$11,$A$2:$A$11,1,$B$2:$B$11,40),IF(AND(A2=1,B2=20),SUMIFS($C$2:$C$11,$A$2:$A$11,1,$B$2:$B$11,20),IF(AND(A2=2,B2=40),SUMIFS($C$2:$C$11,$A$2:$A$11,2,$B$2:$B$11,40),C2))))
Thank you buddy for your reply but i was searching for a dynamic formula like arrayformula using which i used one cell and it should apply on complete row and if you are asking that i want solution in this way then yes i want solution like this only and also i have also used pivot table to get data like this and then used GETPIVOTDATA function to get the sum value but I don't want this because it makes google sheet slow so I am searching for the formula which can solve this problem
 
Upvote 0
@new1 when you want something for Google Sheets, you should clearly state that in you original post & all such questions should be posted to the General Discussion & Other Applications section of the board

I have moved it for you & amended the thread title, this time.
 
Upvote 0
okk so, for which purpose this thread option is given?
 
Upvote 0
Sorry, but I don't understand what you are asking.
 
Upvote 0
actually I am asking that, you were saying the type of question i asked should be posted in general discussion & other application section, so I am asking why is their a option of creating of new thread in this website.
 
Upvote 0
You can start a new thread in any part of the site, but it's split into different sections. As you are asking about a formula for Google Sheets you need to post it into the general discussion & other application section because that is where it belongs
This forum is for discussion about any application & for questions about applications other than Excel or Access.
This is also the place to ask about other spreadsheet programs (e.g. Google Sheets), book suggestions, or other websites.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top