00:00This is one question I do not get on regular basis. How do you sum the numbers by colors?
00:06Let's say, for example, if you have a matrix of cell here with numbers in it and each of the
00:10numbers has a color in it, how do you, for example, add all these numbers in the red cell like
00:17this
00:17and then present it down here? Well, this is how I do it. Right-click your worksheet,
00:22view code, and then you scroll all the way up and go to your VBA projects for your workbook,
00:28right-click on it, insert module. In here, you're going to paste this VBA function. If you'd like a
00:34copy of this VBA function, please leave a request on the comment section and I'll send it to you.
00:39Let me dissect this VBA function a bit here first. So basically, the function name is called sumbycolor
00:46and the first argument is the range sum, which is going to be the cell that you select. Like,
00:53for example, it's going to be from B2 colon all the way down to N13 right at the bottom here.
01:00So it's basically going to be a range of selection or the matrix that you want to sum.
01:05Second argument would be the range color, which I'm going to select as the red here,
01:10which is going to be P3 here. So that's how you use your sumbycolor. And going further into the
01:17function here, the first three lines basically defines a variable called cell,
01:21which is associated with the data type range, which is essentially the location where your
01:26cell is with row and column in it. Sum result will house your summation value. Let's say,
01:32for example, if you're adding all the numbers in red cells, this will house those values.
01:37And then the cell color is basically the color of the cell, which VBA denotes with a long as a
01:45data type. The next line on that VBA function will be the sum result equals zero. This is basically
01:51initializing the sum result to be zero so that we can start doing addition from zero itself.
01:58Next is a follow loop, which goes through every cell that you have selected in your selection range
02:05in this here, range sum, which comes as an argument as a first argument on sumbycolor.
02:09And then it goes through the first cell, which is stored as a range, which is row and column.
02:15And then if you go a bit further here, basically it interrogates the cell color on that particular
02:21cell itself. Let's say the very first cell is B2. So this statement will return cyan and therefore the
02:27variable cell color will store cyan as long or numeric value. Then you'll compare that with the color
02:36that you have selected in the second argument. Let's say you have selected red. If they are equal,
02:41so cyan equal to red. If it's not true, this submission function will be skipped until it finds
02:48the very first red, which is going to be J2 over here. Then you will take zero plus 65 will
02:57equal to 65.
02:58And then the loop continues as it goes through line by line by line. And you will only add colors,
03:05which is specified as this. For our first example would be the red. And once that's done,
03:11the cumulative total right here is returned as a value from the function using this line here like
03:18this. Let's Ctrl S to save it, close the VBA editor. And after that, we can start using that function
03:24using equal sumbycolor. As you can see, it is automatically filling. You can press tab to fill the
03:31rest of the text. And then the first argument will be the range for the matrix like this. It's going
03:36to be for the matrix B2 all the way to N13 here. You're going to press F4 to make both
03:41row and column
03:42fix. And the second argument will be the color cell, which is going to be B3 here. Close parenthesis and
03:48hit enter. And it will sum up all the numbers that's inside the red cell for me. Now, if I
03:54were to apply
03:54the same formula to the rest of the rows, it does the same thing for yellow, green, cyan, blue, and
04:01magenta.
04:01That's it.
Comments