Help with counting conditionally formatted cells using countifs

kaskade

New Member
Joined
Dec 2, 2011
Messages
6
Hi all,

I use this forum constantly and always find a post that will answer my Excel dilemma but after 5 horus of googling and trying options I am close to admitting defeat.

I have a large matrix with 2000 rows and 20 column. 10 of these columns are conditionally formatted to be white, green or red depending on the value in the cell. Two conditional forumlas are applied to each column that are doing a vlookup on another sheet. Like this:

=(VLOOKUP($M1, 'SheetPS'!$A$4:$B$71, 2, 0)>10) - these are green
=(VLOOKUP($M1, 'SheetPS'!$A$4:$B$71, 2, 0)=1) - these are red

I have come to the conclusion that there no functions out there that can count up the cells based on the conditional formatting function but I see lots of people suggesting to use CountIFS to count up based on the criteria but I cant get this working.

What what I need to count is if the result of the vlookup >10 then that is 1 and then add on the next 9 colums that all lookup a different cell on a different sheet

I am probably starting to hallucinate at this stage and I cant figure out how to incorporate even one of these vloookups in the countIF/CountIFs and every thing I try just give me an error in the formula. I thought it would be like this
countif(VLOOKUP($M1, 'SheetPS'!$A$4:$B$71, 2, 0), 1) (not sure how to do the greater than option)

I would be very great for any help
Caroline
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
I have tried other option, I now dont get an error but I get 0 when I expect to get 1

COUNTIFS(VLOOKUP($M2, 'SheetPS'!$A$4:$B$71, 2, 0)>10, true)

COUNTIFS(M2, VLOOKUP($M2, 'Permission Schemes'!$A$4:$B$71, 2, 0) ->the vlookup evaluates to true which is what I would expect but the cell M2 is a string value so I am not sure how this can work)
 
Upvote 0
I can see why you're doing what you're doing, but the format for the COUNTIFS is wrong, so it won't work as you expect. It's a little tricky when doing lookups/counts from other sheets, but this might work for you. If your SheetPS sheet looks like:

AB
1
2
3
4abc2
5def4
6ghi6
7jkl8
8mno10
9pqr12
10stu13
11vwx14
12yzz20
13

<tbody>
</tbody>
SheetPS



then this should work:

=SUM(COUNTIFS(SheetPS!$A$4:$A$71,M2:M10,SheetPS!$B$4:$B$71,">10"))

entered with Control+Shift+Enter,

where M2:M10 is the range of the values you want to check.
 
Last edited:
Upvote 0
Thanks so Much Eric, this worked a treat

=SUM(
COUNTIFS('Issue Type Schemes'!$A$4:$A$206,$J2167,'Issue Type Schemes'!$B$4:$B$206,">10"),
COUNTIFS('Workflow Schemes'!$A$4:$A$557,$K2167,'Workflow Schemes'!$B$4:$B$557,">10"),
COUNTIFS('Issue Type Screen Scheme'!$A$4:$A$265,$L2167,'Issue Type Screen Scheme'!$B$4:$B$265,">10"),
COUNTIFS('Permission Schemes'!$A$3:$A$66,$M2167,'Permission Schemes'!$B$3:$B$66,">10"),
COUNTIFS('Notifications Schemes'!$A$4:$A$19,$N2167,'Notifications Schemes'!$B$4:$B$19,">10"),
COUNTIFS('Field Configuration Schemes'!$A$4:$A$71,$O2167,'Field Configuration Schemes'!$B$4:$B$71,">10"), COUNTIFS($I2167, "")
)
 
Upvote 0
Whoa! That's a bit bigger than what I posted! :eek: But it's clear you grasped the concept and ran with it. Way to go! :cool:
 
Upvote 0
Whoa! That's a bit bigger than what I posted! :eek: But it's clear you grasped the concept and ran with it. Way to go! :cool:

Spoke too soon,

So I have two sets if criteria I need to count up, the one posted above works fine but the following where I need to count where the vlookup value is exactly one is not returning accurate data. Hopefully its something glaringly obvious!

=SUM(
COUNTIFS('Issue Type Schemes'!$A$4:$A$215,$J3,'Issue Type Schemes'!$B$4:$B$215,1),
COUNTIFS('Workflow Schemes'!$A$4:$A$654,$K3,'Workflow Schemes'!$B$4:$B$654,1),
COUNTIFS('Issue Type Screen Scheme'!$A$4:$A$265,$L3,'Issue Type Screen Scheme'!$B$4:$B$265,1),
COUNTIFS('Permission Schemes'!$A$3:$A$66,$M3,'Permission Schemes'!$B$3:$B$66,1),
COUNTIFS('Notifications Schemes'!$A$4:$A$19,$N3,'Notifications Schemes'!$B$4:$B$19,1),
COUNTIFS('Field Configuration Schemes'!$A$4:$A$71,$O3,'Field Configuration Schemes'!$B$4:$B$71,1), COUNTIFS($I3, ">0")
)
 
Upvote 0
Sorry, I don't see anything significantly different from your previous working formula.
 
Upvote 0
Sorry, I don't see anything significantly different from your previous working formula.

I think the problem was sheer exhaustion, went to bed abs this morning is was glaringly obvious that I had the wrong row referenced I started with j3 instead of j2!!!

Thanks again, this was a huge help.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top