Exclude Specific Colored Rows in a SUMIF Function

Hey all,

I'll try to make this brief. Been working on it for a while and just can't get it figured out.

I have two separate sheets, one with data (C-Ports) and one with the result (C-Total). On my data page, if the text or cell (whichever is easier) is red, I do not want my SUMIF function on the results page to reflect that data. Here is my SUMIF function:
=SUMIF('C-Ports'!A$2:A$19995,A3,'C-Ports'!J$2:J$19995)

I have a VB script that will exclude specific colors, but I can't get it to work. Keep in mind, the data from "C-Ports" page is constantly changing. That specific data is populated by multiple other sheets. Here is my VB script.

Please Login or Register  to view this content.

How can I make this work? I want to add up everyone from my "C-Ports" page except specific rows that are in red. Those rows will change over time.

Are the cells colored Red via Conditional formatting?
If so, what is the condition that triggers the red format?
That criteria can be added into the SUMIF (or sumifS) function.

Originally Posted by Jonmo1

Are the cells colored Red via Conditional formatting?
If so, what is the condition that triggers the red format?
That criteria can be added into the SUMIF (or sumifS) function.

The red text/cells are manually assigned according to specific information.

OK, so they're colored by hand, not conditional formatting.

Can you expand on this?

Originally Posted by tshives26

according to specific information.

What is the criteria used to determine which cells get colored?
That can then be used in a formula.


In short, a formula can't sum by color.

However, you CAN use AUTOFILTER to filter by a specific color.
Then use subtotal to sum the visible rows after the filter

=SUBTOTAL(109,A1:A100)

Originally Posted by Jonmo1

OK, so they're colored by hand, not conditional formatting.

Can you expand on this?

It's a certain cell that has a particular number. That number could be anything, as it has to do with bandwidth available on a router and if their is a lock on that particular router. So it isn't a specific value that we could weed out, and the only thing that makes it different right now is the color of the text. Maybe adding a new column with an "X" could help? Any other ideas?

Originally Posted by tshives26

Maybe adding a new column with an "X" could help? Any other ideas?

Yes, that certainly would be the simplest solution (and probably preferred)

Than you can do
=SUMIF(A1:A100,"x",B1:B100)
That sums the values in column B where an x was placed in column A.

Originally Posted by tshives26

So it isn't a specific value that we could weed out

There must be a method/logic to which cells are colored.
They got colored intentionally by hand at one point or another, right?
What was it that indicated cell A should be colored, but not cell B?
There must be something, surely they're not just colored 'randomly'.
You'd be surprised what kind of obscure criteria can be used to accomplish this.

Originally Posted by Jonmo1

=SUMIF(A1:A100,"x",B1:B100)
That sums the values in column B where an x was placed in column A.

Is there a way that we can reverse this? Say if a column has an X, don't have it sum those ones?

Originally Posted by Jonmo1

There must be a method/logic to which cells are colored.
They got colored intentionally by hand at one point or another, right?
What was it that indicated cell A should be colored, but not cell B?
There must be something, surely they're not just colored 'randomly'.
You'd be surprised what kind of obscure criteria can be used to accomplish this.

Well I have Red lines and Blue lines that I do not want in the total SUM. The red are either locked routers or there is a problem/conflict with them in a completely separate program. Also if the router is not in service it will be red. The blue lines are new routers where projects are order for them, but none have been completed yet. That is all manually entered via text color when I see flags go off in other programs I use.

Well, going back to the macro.

Your post says you want to EXclude the rows that are colored a specific color.
That code you posted does the opposite, it INcludes the rows that are colored a specific color.

You would change it this way

Please Login or Register  to view this content.

And you would use it like
=ColorFunction(A1,A1:A100,TRUE)

A1 is a cell that is the color you want to exclude
A1:A100 is the range that gets summed.
TRUE means it will SUM the values (False would mean COUNT the values)

Originally Posted by tshives26

Is there a way that we can reverse this? Say if a column has an X, don't have it sum those ones?

That was my bad, I had forgotten that you were 'Excluding' the colored cells.
in that case it would be
=SUMIF(A1:A100,"<>x",B1:B100)

Originally Posted by Jonmo1

A1 is a cell that is the color you want to exclude
A1:A100 is the range that gets summed.
TRUE means it will SUM the values (False would mean COUNT the values)

How would I add the SUMIF to that though? The SUMIF finds if the router belongs to a certain state, then adds up everything in each state individually.

Originally Posted by tshives26

That is all manually entered via text color when I see flags go off in other programs I use.

OK, so it's not based on any information in the sheet/book.
It's based on observations of events 'in the real world'.

That is a whole other story. lol.

It really is best to use a different method to 'flag' which cells should or should not be summed.
An x or other meaningful text string in a column is certainly a good method.

Originally Posted by tshives26

The SUMIF finds if the router belongs to a certain state, then adds up everything in each state individually.

So it's more than just the color alone that determines which cell to sum.

Stick with the x in a column, and sumif.

Then you can use sumifS for the additional criteria
=SUMIFS(A1:A100,B1:B100,"x",C1:C100,"California")

Sums column A if B=X and C=California

Originally Posted by Jonmo1

So it's more than just the color alone that determines which cell to sum.

Stick with the x in a column, and sumif.

Then you can use sumifS for the additional criteria
=SUMIFS(A1:A100,B1:B100,"x",C1:C100,"California")

Sums column A if B=X and C=California

Hmm interesting. We'll I'll give it a shot when I get to work tomorrow. Thank you for all your help! Hope it works out and there isn't a need to come back

Glad to help.
And again, I forgot you're 'excluding'....
=SUMIFS(A1:A100,B1:B100,"<>x",C1:C100,"California")

Originally Posted by Jonmo1

Glad to help.
And again, I forgot you're 'excluding'....
=SUMIFS(A1:A100,B1:B100,"<>x",C1:C100,"California")

Hey Jonmo,

I'm having difficulties formatting this SUMIFS statement. Here is my SUMIF statement again:
=SUMIF('C-Ports'!B$2:B$19995,A3,'C-Ports'!K$2:K$19995)

Now on the Ports/Data page, Column B has the state names. In the Total/Results page, the city name is in column A and our SUMIFS statement will be in column B. I see you need two arguments to start off the SUMIFS. I wasn't sure of the other variable so I gave this a try (wrong):
=SUMIFS('C-Ports'!B$2:B$19995,'C-Ports'!B$2:B$19995,"<>x",'C-Ports'!K$2:K$19995,"Albany")

Could you be so kind to help out with the formatting please?

In SUMIFS, the syntax is

=SUMIFS(rangetoSUM,criteria1range,criteria1,criteria2range,criteria2)

Which column contains the x that flag's the row instead of coloring?
Which column contains the city names?
Which column do you actually want to sum?

Originally Posted by Jonmo1

Which column contains the x that flag's the row instead of coloring?
Which column contains the city names?
Which column do you actually want to sum?

Col A on C-Ports holds the X.
Col B on C-Ports has the City name.
The sum is rather confusing as it is a couple different factors. I believe the SUMIF shows what is trying to be summed, correct?

The A3 in my SUMIF statement is for matching the city name in col A on the C-Total page to the city name on Col B on the C-Ports page.

The SUMIF you posted is summing column K

Try
=SUMIFS('C-Ports'!K$2:K$19995,'C-Ports'!A$2:A$19995,"<>x",'C-Ports'!B$2:B$19995,"Albany")

Works perfect!!!! Thank you so much for your help!

You're welcome.

Originally Posted by Jonmo1

You're welcome.

Sorry to bother again!!

Would it be possible to add two conditions?

For example, right now if there is an X, it doesn't add the row. Is there a way to make it where if there is an X or a Y, that they both will trigger the row not to be included in the SUMIFS? Or can you only have one condition. Thanks!

So if it's x OR y, then the row should NOT be summed?

=SUMIFS('C-Ports'!K$2:K$19995,'C-Ports'!A$2:A$19995,"<>x",'C-Ports'!A$2:A$19995,"<>y",'C-Ports'!B$2:B$19995,"Albany")

Originally Posted by Jonmo1

So if it's x OR y, then the row should NOT be summed?

=SUMIFS('C-Ports'!K$2:K$19995,'C-Ports'!A$2:A$19995,"<>x",'C-Ports'!A$2:A$19995,"<>y",'C-Ports'!B$2:B$19995,"Albany")

Again, my man. Thanks for all your help! Have a great weekend.