Match or Match If

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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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?

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.
 
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")

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

In B2 of Sheet3 enter and copy down:

=COUNTIFS(Sheet2!A:A,$A2,Sheet2!B:B,"Y")
 
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")

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

=COUNTIFS(Sheet2!A$2:A$1517,A2,Sheet2!B$2:C$1517,"Y")
 
Upvote 0
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.

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

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

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

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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