Count Question

C

cionnaith

Board Regular
Joined
Mar 9, 2010
Messages
52
Good day folks!

I have another question about counting in cells.

I need to count across four adjacent cells (e.g. A1:A4) where each of these cells can have one of three nominal entries (i.e. green, yellow, or red). I need an entry of green to be equal to 2, an entry of yellow equal to 1 and an entry of red equal to 0. Once that is done, I need the sum of those values assigned divided my the maximum possible across the four cells...in this case 8.

Hope this makes sense...

Thanks in advance!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
cionnaith said:
Good day folks!

I have another question about counting in cells.

I need to count across four adjacent cells (e.g. A1:A4) where each of these cells can have one of three nominal entries (i.e. green, yellow, or red). I need an entry of green to be equal to 2, an entry of yellow equal to 1 and an entry of red equal to 0. Once that is done, I need the sum of those values assigned divided my the maximum possible across the four cells...in this case 8.

Hope this makes sense...

Thanks in advance!
Click to expand...
One way...

Create a little 2 column table like this...


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">green</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">yellow</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">red</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">0</TD></TR></TBODY></TABLE>


Then, this formula:

=SUMPRODUCT(SUMIF(E1:E3,A1:A4,F1:F3))/8
 
Upvote 0
That works. Thank you. One other question, though. If there are no entries in the cells A1:A4, how do I get the cell that the count and percentage are being done in return an empty cell rather than 0%?

Also, in addition to that cell that reads from the table for total across the four adjacent cells, I need to count only number of "greens", "yellows" and "reds" divided by 4 in three separate cells and get those three calculations to return empty cells instead of 0% if there are no entries made.

Thank you so much for your help!
 
Upvote 0
cionnaith said:
That works. Thank you. One other question, though. If there are no entries in the cells A1:A4, how do I get the cell that the count and percentage are being done in return an empty cell rather than 0%?

Also, in addition to that cell that reads from the table for total across the four adjacent cells, I need to count only number of "greens", "yellows" and "reds" divided by 4 in three separate cells and get those three calculations to return empty cells instead of 0% if there are no entries made.

Thank you so much for your help!
Click to expand...
For Q1:

=IF(COUNTA(A1:A4),SUMPRODUCT(SUMIF(E1:E3,A1:A4,F1:F3))/8,"")

For Q2:

=IF(COUNTA(A1:A4),COUNTIF(A1:A4,E1)/4,"")
=IF(COUNTA(A1:A4),COUNTIF(A1:A4,E2)/4,"")
=IF(COUNTA(A1:A4),COUNTIF(A1:A4,E3)/4,"")
 
Upvote 0
T. Valko said:
For Q1:

=IF(COUNTA(A1:A4),SUMPRODUCT(SUMIF(E1:E3,A1:A4,F1:F3))/8,"")

For Q2:

=IF(COUNTA(A1:A4),COUNTIF(A1:A4,E1)/4,"")
=IF(COUNTA(A1:A4),COUNTIF(A1:A4,E2)/4,"")
=IF(COUNTA(A1:A4),COUNTIF(A1:A4,E3)/4,"")
Click to expand...

=IF(COUNTA(A1:A4),COUNTIF(A1:A4,E1)/4,"") is returning "FALSE" in the cell :(

Sorry to be such a pain...I really appreciate your help :)
 
Upvote 0
I just realized that there is one other condition I need to apply to these cell calculations. For the count and calculation of percentage, I need the formula to ignore any of the four cells with the "red", "yellow" or "green" entries possible that are left blank. So, going across those four cells (in my first calculation question) I need to divide by the total possible of the cells that are actually completed instead of 8 (as I had originally set it up). For example, for the four cells, if there are entries in only three of the four cells, I need to automatically divide by 6 instead of 8 (4 for two cell entries, 2 for one cell entry).

I guess I just continue to be a pain :( Thanks though!!
 
Upvote 0
cionnaith said:
I just realized that there is one other condition I need to apply to these cell calculations. For the count and calculation of percentage, I need the formula to ignore any of the four cells with the "red", "yellow" or "green" entries possible that are left blank. So, going across those four cells (in my first calculation question) I need to divide by the total possible of the cells that are actually completed instead of 8 (as I had originally set it up). For example, for the four cells, if there are entries in only three of the four cells, I need to automatically divide by 6 instead of 8 (4 for two cell entries, 2 for one cell entry).

I guess I just continue to be a pain :( Thanks though!!
Click to expand...
I think this is what you're after...

=IF(COUNTA(A1:A4),SUMPRODUCT(SUMIF(E1:E3,A1:A4,F1:F3))/(COUNTA(A1:A4)*2),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Macro to find and count between tabs based on highlighted color
Replies
5
Views
429
Jeffrey Mahoney
Jeffrey Mahoney
C
  • Solved
Conditional Formatting Assistance
2
Replies
16
Views
611
felixstraube
F
R
  • Solved
Keeping score on a quiz
2
Replies
12
Views
458
dreid1011
dreid1011
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
367
kbishop94
K
V
  • Solved
Conditional Formatting Formula
Replies
2
Views
1K
awoohaw
awoohaw

Forum statistics

Threads
1,224,602
Messages
6,179,848
Members
452,948
Latest member
UsmanAli786

Share this page

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