Need Counting Formula

C

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
I would really appreciate a formula for column G,that will count the number of columns and rows that the 5 values in Columns B:F with the values in table in bottom post. I gave a couple of examples.
Excel Workbook
ABCDEFG
108/25/115131518225C3R
208/24/114111833344C4R
308/23/11112313334
408/22/1148182627
508/21/1137171828
608/20/11414213134
Sheet1
Excel 2007

Ticket numbers
Excel Workbook
HIJKLM
1123456
2789101112
3131415161718
4192021222324
5252627282930
6313233343536
Sheet1
Excel 2007
 
Sort by date Sort by votes

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Domenic said:
Try...

G1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(MMULT(COLUMN($H$1:$M$6)^0,IF(ISNUMBER(MATCH($H$1:$M$6,$B1:$F1,0)),1,0))>0,1))&"C"&SUM(IF(MMULT(IF(ISNUMBER(MATCH($H$1:$M$6,$B1:$F1,0)),1,0),ROW($H$1:$M$6)^0)>0,1))&"R"
Click to expand...

Can the above formula be adapted to use in this worksheet? I would like to return each column heading the values in U:Y fall in. I show examples in Cells Z & AA
Excel Workbook
TUVWXYZAA
1
2812213234C2C6
3411141525C4C5
4923242530
51315172834
6820222336
7
8C1C2C3C4C5C6
9R1123456
10R2789101112
11R3131415161718
12R4192021222324
13R5252627282930
14R6313233343536
Sheet2
Excel 2007
 
Upvote 0
Based on your data, the results for the first two rows should be...

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2853974 class=xl63 height=21 width=64>C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>C4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>C6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C5</TD></TR></TBODY></TABLE>

...not...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" id=td_post_2853974 class=xl66 height=21 width=64>C2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl67 width=64>C6</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 height=21 width=64>C4</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=64>C5</TD></TR></TBODY></TABLE>

Or did I misunderstand your question? Can you please clarify?
 
Upvote 0
Domenic said:
Based on your data, the results for the first two rows should be...<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2853974 class=xl63 height=21 width=64>C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>C4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>C6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C5

...not...<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" id=td_post_2853974 class=xl66 height=21 width=64>C2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl67 width=64>C6</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 height=21 width=64>C4</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=64>C5

Or did I misunderstand your question? Can you please clarify?
Click to expand...

Domenic, thanks for responding. I made a mistake, the first 2 rows will look like this
Excel Workbook
UVWXYZAAABACAD
2812213234C2C2C3C2C4
3411141525C4C5C2C3C1
Sheet2
Excel 2007
 
Upvote 0
CARBOB said:
Domenic, thanks for responding. I made a mistake, the first 2 rows will look like this
Excel Workbook
UVWXYZAAABACAD
2812213234C2C2C3C2C4
3411141525C4C5C2C3C1
Sheet2
Excel 2007
Click to expand...
Excel Workbook
TUVWXYZ
8C1C2C3C4C5C6
9R1123456
10R2789101112
11R3131415161718
12R4192021222324
13R5252627282930
14R6313233343536
Sheet2
Excel 2007

Using the values U2:Y2, return the column heading each value falls under
8-12-21-32-34 C2-C2-C3-C2-C4
 
Upvote 0
Your results are somewhat contradictory. If I understood you correctly, it looks like the results should be as follows...

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 height=21 width=64>8</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" id=td_post_2856518 class=xl63 width=64>12</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>21</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>32</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>34</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C6</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C3</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C4</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>4</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=64>11</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=64>14</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=64>15</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=64>25</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C4</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C5</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C3</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=64>C1</TD></TR></TBODY></TABLE>

If this is correct, try...

Z2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX($U$8:$Z$8,SMALL(IF($U$9:$Z$14=U2,COLUMN($U$9:$Z$14)-COLUMN($U$9)+1),1))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC
A
  • Solved
Formula to calculate total days in a column.
Replies
3
Views
49
asyamonique
A
M
  • Question Question
inputting formulas across multiple sheets with varying number of rows
Replies
3
Views
113
mrider123
M
R
  • Question Question
VBA for Cut, move, paste, delete rows
Replies
6
Views
271
RForster6
R
Z
  • Question Question
Breaking down some dates using a formula
2
Replies
13
Views
554
zelarra
Z

Forum statistics

Threads
1,224,621
Messages
6,179,946
Members
452,949
Latest member
beartooth91

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