Sum cells that are adjacent to conditionally formatted cells

Bijaz

New Member
Joined
Jan 23, 2011
Messages
38
Hello,

I have conditional formatting in column A and a count of those issues in column B. For instance, I would like to sum all fo the cells in column B in which the adjacent cell has conditional formatting (ie. if a cell in column A is red, I would like to sum all of cells adjacent to it in column B) Any recommendations?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
I have conditional formatting in column A and a count of those issues in column B. For instance, I would like to sum all fo the cells in column B in which the adjacent cell has conditional formatting (ie. if a cell in column A is red, I would like to sum all of cells adjacent to it in column B) Any recommendations?

Hi

Use the condition you used in the conditional formatting.
 

Bijaz

New Member
Joined
Jan 23, 2011
Messages
38
Hi, try use SUMIF in conjunction with your Conditional Formatting rules.

This is the formula I used, and it comes back as zero:
=SUMIF(A2:A61, "floor", B2:B61)

Basically, if the a cell in column a contains the word floor, I want to sum the count in column B. Is this correct?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

What's the exact condition you used in conditional formatting?

Do the values in column A contain the word floor by itself, or is it among other text like "the floor is carpeted" ??
If this is the case, try
=SUMIF(A2:A61, "*floor*", B2:B61)

Perhaps the values in column B are not real numbers, but numbers stored as text?
What does this return
=ISNUMBER(B2) - repeat for all values in column B
 

Bijaz

New Member
Joined
Jan 23, 2011
Messages
38
What's the exact condition you used in conditional formatting?

Do the values in column A contain the word floor by itself, or is it among other text like "the floor is carpeted" ??
If this is the case, try
=SUMIF(A2:A61, "*floor*", B2:B61)

Perhaps the values in column B are not real numbers, but numbers stored as text?
What does this return
=ISNUMBER(B2) - repeat for all values in column B


Aha! Worked perfectly! Thanks, and have a great week.
 

Forum statistics

Threads
1,137,296
Messages
5,680,672
Members
419,924
Latest member
Dhamodharan992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top