Match or Match If

E

excelenergy

Board Regular
Joined
Jun 7, 2012
Messages
142
My formula is below. But this is my issue:

I have sheet 1 and sheet 2. I think what I might be trying to do is simple but I'm not sure if it should be a match, match if or index formula.

In sheet 3 in Column A...I have a list of unique names of clothing brands.

In sheet 2 in Column A...I have a list of the same clothing brands except allot of brand names repeat over and over throughout 1500 lines.
In sheet 2 in column B...Its a column that has a Y or an N....


What I'm trying to do is in sheet3, column B - I want my formula to look up the name in column A, then search the name in SHEET2 and find all the times the clothing brand name appears and how many times "Y" appears for that clothing brand then put that number in the cell in sheet3 column B. My formula:

Rich (BB code): 
=INDEX(Sheet2!$b$2:$b$1517,MATCH(sheet3!"a2",$a$2:$a$345))



Some of this formula might be right, but I'm not sure what to add, so it searches through the range and adds up how many times it finds the letter Y. Anyway any help is appreciated.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hi,

If I understand correctly, you just want the COUNT of the Brand with Y:


Excel 2010
ABCDE
1BrandBrandCount w/ Y
2AYA3
3BYB2
4CYC2
5AN
6AY
7BN
8CN
9BY
10BN
11CY
12AY
Sheet11
Cell Formulas
RangeFormula
E2=COUNTIFS(A$2:A$12,D2,B$2:B$12,"Y")


Add Sheet Name to formula, copied down.
 
Upvote 0
Hey thanks for the reply.

You got the right idea. See the unique clothing brand names are listed in sheet3 - lines A2:A345. Each clothing brand is only listed once

In sheet2 lines A2:A1517 it is a list of the same clothing brands except they repeat...For example Nike might appear 25 times throughout the list.
Also in sheet 2 - column B has a Y or an N.

So in sheet3 in column B what I'm trying to get the formula to do is take the value in sheet3 cell A2 (lets say its Nike) and go search for the "Nike" name in Sheet 2, and sum up how many times Iit finds the Nike and the letter Y beside it, and insert that total in Sheet3 cell B2.

Does that make sense? That's why I thought Index/Match?

jtakw said:
Hi,

If I understand correctly, you just want the COUNT of the Brand with Y:

Excel 2010
ABCDE
BrandBrandCount w/ Y
AYA
BYB
CYC
AN
AY
BN
CN
BY
BN
CY
AY

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=COUNTIFS(A$2:A$12,D2,B$2:B$12,"Y")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Add Sheet Name to formula, copied down.
Click to expand...
 
Upvote 0
Oh, I did try the CountIfs, so this is what the formula looks like, and in Sheet3, B2 all the formula returns is #VALUE
Code: 
=COUNTIFS(A$2:A$345,A2,Sheet2!B$2:C$1517,"Y")

jtakw said:
Hi,

If I understand correctly, you just want the COUNT of the Brand with Y:

Excel 2010
ABCDE
BrandBrandCount w/ Y
AYA
BYB
CYC
AN
AY
BN
CN
BY
BN
CY
AY

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=COUNTIFS(A$2:A$12,D2,B$2:B$12,"Y")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Add Sheet Name to formula, copied down.
Click to expand...
 
Upvote 0
ha! Consider this solved! You know what the problem was? Both formulas actually worked...I had a hidden column I forgot about..I unhid it...and adjusted the formula accordingly.

Its all good now. Thank ya!

Aladin Akyurek said:
In B2 of Sheet3 enter and copy down:

=COUNTIFS(Sheet2!A:A,$A2,Sheet2!B:B,"Y")
Click to expand...
 
Upvote 0
excelenergy said:
Oh, I did try the CountIfs, so this is what the formula looks like, and in Sheet3, B2 all the formula returns is #VALUE
Code: 
=COUNTIFS(A$2:A$345,A2,Sheet2!B$2:C$1517,"Y")
Click to expand...

You've adjusted the formula incorrectly, it should be:

=COUNTIFS(Sheet2!A$2:A$1517,A2,Sheet2!B$2:C$1517,"Y")
 
Upvote 0
jtakw said:
Hi,

If I understand correctly, you just want the COUNT of the Brand with Y:

Excel 2010
ABCDE
BrandBrandCount w/ Y
AYA
BYB
CYC
AN
AY
BN
CN
BY
BN
CY
AY

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=COUNTIFS(A$2:A$12,D2,B$2:B$12,"Y")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Add Sheet Name to formula, copied down.
Click to expand...

excelenergy said:
Oh, I did try the CountIfs, so this is what the formula looks like, and in Sheet3, B2 all the formula returns is #VALUE
Code: 
=COUNTIFS(A$2:A$345,A2,Sheet2!B$2:C$1517,"Y")
Click to expand...

Aladin Akyurek said:
The OP states at least twice that column B of Sheet2 has a Y or N, not B:C. Besides, that would be troublesome.
Click to expand...

That wasn't my mistake nor misunderstanding of the OP, I copied his formula from Post #4 to correct his First part, but didn't see his mistake with the 2nd part where he had put column C in.
Check my Post #2 , that's what it was all along.
My mistake was not also catching his 2nd mistake, I didn't use Column C.
 
Upvote 0
You must log in or register to reply here.

Similar threads

ChrisMac1
  • Solved
Formula - Return text from a column if two other columns match
Replies
4
Views
122
Fluff
Fluff
P
  • Solved
Dynamic Sheet Tabs in Index Match function
Replies
9
Views
155
Peter_SSs
Peter_SSs
A
  • Solved
populate summary sheet by merge amounts based on two columns(ID,CUSTOMER)
Replies
3
Views
203
DanteAmor
DanteAmor
R
  • Solved
Approach to sorting and index/match
2
Replies
16
Views
405
Alex Blakenburg
A
srizki
  • Question Question
Sumifs Match Formula.
Replies
5
Views
313
dreid1011
dreid1011

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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