Qiang Qiang~ Hello everyone! I am Satellite Sauce~
The little monster of merging cells has to be pulled out and criticized almost every time when statistics are collected, because it is so annoying!
Advertisement
Today’s story starts with my colleague Xiaowen who had a problem with merging cells…
She made a form like this:
I hope to find the total quantity based on the condition “product”.
Advertisement
I had to give her this formula:
=SUMPRODUCT($D$3$D$11*(F3=LOOKUP(ROW($3$11)IF($B$3$B$11<>"ROW($3$11))$B$3$B$11)))
But this guy was “excessive”: This is too long. Is there a simpler formula?
Well, there really is… Let’s invite our protagonist today——SCAN function!
=SUM((SCAN(,$B$3:$B$11,LAMBDA(x,y,IF(y="",x,y)))=F3)*$D$3:$D$11)
Well, this formula is much easier, right?
1. Understand the SCAN function
Some students may not understand the above formula. After all, there are two unfamiliar functions~
It doesn’t matter, Wei is here to introduce it to you.
SCAN, yes Office 365 The new function in WPS Can also be used in .
What it does is scan:
SCAN has three parameters, the first is the starting value of the accumulator, the second is the array to be scanned, and the third is the function LAMBDA.
LAMBDA is a function that allows us to create our own functions without programming.
A bit convoluted?
Give a chestnut:
=LAMBDA(x,y,x+y)
This formula is a custom function.
What it means is that assuming there are parameters x and y in a function, the operation of x+y is performed on these two parameters, that is, the sum.
Replace x+y with any complex formula and[Define Name]under the[Formula]tab:
In this way, the steps to use this complex formula next time will be greatly reduced.
2. Formula analysis
Now that we know the SCAN function, let’s analyze the formula mentioned above~
Formula review:
=SUM((SCAN($B$3$B$11,LAMBDA(x,y,IF(y="x,y)))=F3)*$D$3$D$11)
❶ LAMBDA(x,y,IF(y=””,x,y)
❷ SCAN(,$B$3:$B$11,❶)
❸ SUM((❷)=F3)*$D$3:$D$11)
❶ Perform IF judgment on the parameters x and y involved in the calculation, that is, when y is empty, the result is output x, otherwise it is output y.
❷ The SCAN function provides ❶ with the selection $B$3:$B$11, and converts the merged cells into an array:
❸ Filter and sum the data range $D$3:$D$11 based on the content in cell F3.
The above formula actually uses the SCAN function to convert the merged cells into a complete array, and then uses SUM to calculate the sum.
Drag the drop-down formula to get the results of other products~
3. Other uses
In addition to merging cells and summing, SCAN has more powerful uses!
❶ Find merged cells
=XLOOKUP(F3&G3,SCAN($B$3$B$11LAMBDA(x,y,IF(y="x,y)))&$C$3$C$11$D$3$D$11)
❷ Find the number of consecutive occurrences
=MAX(SCAN(0$B$3$B$24,LAMBDA(x,y,IF(y=D3,x+1))))
4. Write at the end
Okay, today we mainly share how to use the SCAN function to sum merged cells, and learn more about the usage of this function~