I am trying to figure out in Google Sheets if there is a way to do this.

FROM SHEET1:

TO SHEET2

1. Any time I change the value for ABC to value 0, it should add 10. So for every value in Sheet1, it should add value in sheet2 only when I make the value 0 in Sheet1
2. I have multiple ABC, XYZ in Sheets1, so I want to link each ABC in Sheet2 from Sheet1

I want to see if this is possible in Google Sheets.

CodePudding user response：

ABC =SUM(100 10 15)

XYZ =SUM(100 20 25)

try:

=QUERY(A1:B, "select A,100 sum(B) where B>0 group by A label 100 sum(B)''")

CodePudding user response：

You want to have a SUM per value but also consider the 0s as 10s?

Try this:

={UNIQUE(Sheet1!A:A),BYROW(UNIQUE (Sheet1!A:A),LAMBDA(each,100 SUMIF(Sheet1!A:A,each,Sheet1!B:B) 10*COUNTIFS(Sheet1!B:B,0,Sheet1!A:A,each)))}