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