countifs with cell color as a condition

[ssc]

Hello!

I'm new to forum and was wondering if anyone can help me with a countifs formula with the cell color as a condition. For example, in the attached workbook, on the Scorecard tab in cell C7, E7, G7, and I7, I would like to countifs(Sheet 1 A:A,Scorecard C5, Sheet 1 B:B, Green). Basically, if the name on sheet 1 matches with the name in row 5 of the scorecard tab, then I want to count the cells that are green in column B of sheet 1.

Any help would be greatly appreciated!

 

[Ramesh]

Is there any criteria using which you are marking those cells as Green?

If there is a specific criteria then, you can use that criteria along with Countifs function to get the value in C7, E7, G7 and I7

 

[ssc]

they are marked with "X"s but, not all of the cells marked with "X" in the column are colored green.

 

[Sam Thompson]

As a quick fix, put a filter on the data range Ribbon/Data/Filter, Then on the filter drop down go to sort by colour. Choose all the green ones and put an x in those cells. Now do your COUNTIFS().

If this is a repeating spreadsheet, maybe conditionally format so if they put an x in a cell it turns green. Might save you some time later on.

 

[ssc]

Thanks but only certain lines with "X"s should be marked green. I'm not trying to mark all of the "X"s green. I'm trying to count the number of green cells associated with a certain name. So, if the names on sheet 1, matches the name on the Scorecard tab, then I want to count the green cells associated with that name. The green cells are marked with an "X," but other cells in that same column are also marked with an "X." Not all cells marked with an "X" are highlighted green.

thanks.

 

[Sam Thompson]

Ok, I understand. you have two choices. There is the VBA route which is pretty easy to find on the web. There is a quite detailed post on it on the abelbits website - I would post the link but I always seem to get into trouble if I post links on forums. The second route is to use a helper column. Do a sort on the colour like I said above and then put whatever identifier you want in a parallel column. Then COUNTIF() that column.

 

[Hui]

This has been asked before
Did you try the search box at the top right of this screen?

 

[bosco_yip]

Try………



1] In "Scorecard" Sheet M3, green color background with "X" as criteria (same as "Sheet 1" Column B green color with "X").

2] Select "Scorecard" Sheet C7 >> Define Name >>

>> Name : CountGreenWithX

>> Refer to :

=SUMPRODUCT((Sheet1!$A$1:$A$50=B$5)*(Sheet1!$B$1:$B$50=$M$3)*(GET.CELL(63,IF(1,+OFFSET(Sheet1!$B$1:$B$50,ROW(Sheet1!$B$1:$B$50)-MIN(ROW(Sheet1!$B$1:$B$50)),)))=GET.CELL(63,$M$3)))

3] In "Scorecard" Sheet C7, formula copied right to E7, G7 and I7 :

=CountGreenWithX

p.s. : Get.Cell() is a Excel 4 Macro function, so you need to save file as xlsm.

Regards
Bosco

 

[Derek McGill]

Hi bosco_yip, Can you change "=SUMPRODUCT((Sheet1!$A$1:$A$50=B$5)*(Sheet1!$B$1:$B$50=$M$3)*(GET.CELL(63,IF(1,+OFFSET(Sheet1!$B$1:$B$50,ROW(Sheet1!$B$1:$B$50)-MIN(ROW(Sheet1!$B$1:$B$50)),)))=GET.CELL(63,$M$3)))" to Row B3-S3 for me ?

 

[bosco_yip]

Hi bosco_yip, Can you change "=SUMPRODUCT((Sheet1!$A$1:$A$50=B$5)*(Sheet1!$B$1:$B$50=$M$3)*(GET.CELL(63,IF(1,+OFFSET(Sheet1!$B$1:$B$50,ROW(Sheet1!$B$1:$B$50)-MIN(ROW(Sheet1!$B$1:$B$50)),)))=GET.CELL(63,$M$3)))" to Row B3-S3 for me ?

Maybe..........



1] Input table : Sheet1 A1:S3

2] Output table : same as post #8.

3] Defined name formula, changed to :

=SUMPRODUCT((Sheet1!$B$1:$S$1=B$5)*(Sheet1!$B$3:$S$3=$M$3)*(GET.CELL(63,IF(1,+OFFSET(Sheet1!$B$3:$S$3,,COLUMN(Sheet1!$B$3:$S$3)-MIN(COLUMN(Sheet1!$B$3:$S$3)))))=GET.CELL(63,$M$3)))

4] See revised attachment.

Regards
Bosco

 

[Sam Thompson]

Isn't the solution to force structure to the data? All it takes is for a different shade of green to be used and the number is different. I would have thought that its a case of going back to the source and putting some validation around the cells instead if its to be an ongoing issue. If colour is something that's wanted then sure put some conditional formatting but using macros seems to be a convoluted work around to a simpler solution.

 

[Hui]

Isn't the solution to force structure to the data? All it takes is for a different shade of green to be used and the number is different. I would have thought that its a case of going back to the source and putting some validation around the cells instead if its to be an ongoing issue. If colour is something that's wanted then sure put some conditional formatting but using macros seems to be a convoluted work around to a simpler solution.

I would recommend that an extra field is added so that it is very clear that it is a user defined field and doesn't have dependencies on existing data.
It also then becomes easier to add Data Validation to force valid entries