Data Range Manipulations Skipping over unwanted cells.

Hi all,

Below I made an example of a possible arrangement of data.

Snipet.JPG

Actually the data set is larger but for these purposes it will work.

Is there a way I can easily sum of the different percentages in another cell with a formula so a check can be done on the total percentages formatted in red and black?
I know I can just select each cell, but the file is full of this kind of arrangement, and the data set is large enough to make the hunt and peck technique a hassle.

For this case the sum of all the percentages in red & black would be 55%.
The total sum of all numbers would be 65.

Hi, and welcome to the forum

Rather than adding a picture upload your workbook, or at least a representative sample of it, and manually add the results you expect. Not many of us are inclined to spend time creating a test workbook when you already have one.

Here's a possibility, it could also be made into a function.

Please Login or Register  to view this content.

..another way if the red %s are always in even numbered columns and black percentages in odd columns and %s are all less than 100% and numbers >=1

Black % as an array formula entered with Ctrl Shift Enter

Formula:

Please Login or Register  to view this content.

Red % as an array formula entered with Ctrl Shift Enter

Formula:

Please Login or Register  to view this content.

numbers total

=SUMIF(A1:D10,">=1")