If, countif, index

J

joetogo

New Member
Joined
Apr 16, 2017
Messages
12
Hi team,

I feel like I'm not that far off from what i want to achieve, but what I'm essentially trying to do is
have multiple criteria reference a table where there are 2 criteria are dynamic ref cells.

With Sheet2, i managed to get it to work somewhat, but it the result doesn't show the first Service
for the customer in the table. As in it lists all the correct services except for the first. I'm not sure how
to get the second criteria "Category" to work either.

Sheet1's formula does what I want it to, but instead of displaying all the relevant records, it dupes the
same record.

https://www.dropbox.com/s/6sw2aaxdk0hu1v0/TestSheet.xlsx?dl=0

Can anybody please help?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Re: If, countif, index help!! :(

Try this:
Drag formula down as needed. This is an array formula that must be entered with CTRL-SHIFT-ENTER.

You were not picking up the first item because your row formula ROW($A$2:$A$16) was returning
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16} and not starting with 1.

Excel Workbook
ABCDEFG
1CompanyService NameCategoryCriteriaOutput
2Parliamentary ServiceWAN Service Lite@SILVERNetworkCompanyServices
3Parliamentary ServiceVoice Connect@SILVERVoiceParliamentary ServiceWAN Service Lite@SILVER
4Parliamentary ServiceWAN Service Premium@GOLDNetworkCategoryVoice Connect@SILVER
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetworkVoice
6Parliamentary ServiceWAN Service Premium@SILVERMobile
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork
25IT
26Mobile
Sheet
 
Upvote 0
Re: If, countif, index help!! :(

Care to post precisely list what the desired results are when Company = Ministry of Justice and Category = Network?
 
Upvote 0
Re: If, countif, index help!! :(

Aladin Akyurek said:
Care to post precisely list what the desired results are when Company = Ministry of Justice and Category = Network?
Click to expand...

Hi Aladin,

I'd like a list of the "Services" when the Company = MOJ and Category = Network. So the list will be:

TaaS GNet Site Connectivity@MOJNZXA
WAN Service Premium@GOLD
WAN Service Foundation@GOLDY

Company and Category will be dynamic affecting the list content and length as they change. Hope that clarifies?
 
Upvote 0
Re: If, countif, index help!! :(

AhoyNC said:
Try this:
Drag formula down as needed. This is an array formula that must be entered with CTRL-SHIFT-ENTER.

You were not picking up the first item because your row formula ROW($A$2:$A$16) was returning
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16} and not starting with 1.

ABCDEFG
1CompanyService NameCategory Criteria Output
2Parliamentary ServiceWAN Service Lite@SILVERNetwork Company Services
3Parliamentary ServiceVoice Connect@SILVERVoice Parliamentary Service WAN Service Lite@SILVER
4Parliamentary ServiceWAN Service Premium@GOLDNetwork Category Voice Connect@SILVER
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork Voice
6Parliamentary ServiceWAN Service Premium@SILVERMobile
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork
25 IT
26 Mobile

<colgroup><col style="width:30px; "><col style="width:130px;"><col style="width:227px;"><col style="width:78px;"><col style="width:64px;"><col style="width:143px;"><col style="width:64px;"><col style="width:256px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G3{=IF(ISERROR(INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,IF($C$2:$C$16=$E$5,ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2)),"",INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($G$3:G3)),2))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Hi AhoyNC,

I've tried your formula above, but now I'm coming up with a blank result? I've gone through the formula
evaluation and comes up blank also? Is entered as an array formula.

Thank you for your help!
 
Upvote 0
Re: If, countif, index help!! :(

I downloaded your file as you can see it works for me. Not sure why you are getting blanks.
Excel Workbook
ABCDEFG
1CompanyService NameCategoryCriteriaOutput
2Parliamentary ServiceWAN Service Lite@SILVERNetworkCompanyServices
3Parliamentary ServiceVoice Connect@SILVERVoiceMinistry of JusticeTaaS GNet Site Connectivity@MOJNZXA
4Parliamentary ServiceWAN Service Premium@GOLDNetworkCategoryCorporate Mobile Direct@GOLD
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetworkNetworkeFax@SILVER
6Parliamentary ServiceWAN Service Premium@SILVERMobile
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork
Sheet
 
Upvote 0
Re: If, countif, index help!! :(

AhoyNC said:
I downloaded your file as you can see it works for me. Not sure why you are getting blanks.

ABCDEFG
1CompanyService NameCategory Criteria Output
2Parliamentary ServiceWAN Service Lite@SILVERNetwork Company Services
3Parliamentary ServiceVoice Connect@SILVERVoice Ministry of Justice TaaS GNet Site Connectivity@MOJNZXA
4Parliamentary ServiceWAN Service Premium@GOLDNetwork Category Corporate Mobile Direct@GOLD
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork Network eFax@SILVER
6Parliamentary ServiceWAN Service Premium@SILVERMobile
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork

<colgroup><col style="width:30px; "><col style="width:130px;"><col style="width:227px;"><col style="width:78px;"><col style="width:64px;"><col style="width:143px;"><col style="width:64px;"><col style="width:256px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G3{=IF(ISERROR(INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,IF($C$2:$C$16=$E$5,ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2)),"",INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($G$3:G3)),2))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Hmmm, I'll double check, I'm doing something wrong :)

Thank you once again! I'll message when i successfully get it to work :D
 
Upvote 0
Re: If, countif, index help!! :(

AhoyNC said:
I downloaded your file as you can see it works for me. Not sure why you are getting blanks.

ABCDEFG
1CompanyService NameCategory Criteria Output
2Parliamentary ServiceWAN Service Lite@SILVERNetwork Company Services
3Parliamentary ServiceVoice Connect@SILVERVoice Ministry of Justice TaaS GNet Site Connectivity@MOJNZXA
4Parliamentary ServiceWAN Service Premium@GOLDNetwork Category Corporate Mobile Direct@GOLD
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork Network eFax@SILVER
6Parliamentary ServiceWAN Service Premium@SILVERMobile
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork

<colgroup><col style="width:30px; "><col style="width:130px;"><col style="width:227px;"><col style="width:78px;"><col style="width:64px;"><col style="width:143px;"><col style="width:64px;"><col style="width:256px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G3{=IF(ISERROR(INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,IF($C$2:$C$16=$E$5,ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2)),"",INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($G$3:G3)),2))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...


Hi AhoyNZ,

One thing though is that the categories don't match when you change the criteria in the dynamic cells.
For e.g.

Company = Parliamentary Service & Category = Network should result in:

WAN Service Lite@SILVER
WAN Service Premium@GOLD
WAN Service Delivery Platform@SILVER
WAN Service Delivery Platform@SILVER (is a dupe, unsure why that is there as it shouldn't)

Does that make sense?

Thank you once again for your assistance.
 
Upvote 0
Re: If, countif, index help!! :(

I see the problem. I left out an IF statement in the second part of the formula.

Should be:
Code: 
[TABLE="width: 1531"]
<colgroup><col width="1531"></colgroup>[TR]
   [TD="width: 1531"]=IF(ISERROR(INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,IF($C$2:$C$16=$E$5,ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2)),"",INDEX($A$2:$C$16,SMALL(IF($A$2:$A$16=$E$3,[COLOR=#ff0000]IF($C$2:$C$16=$E$5[/COLOR],ROW($A$2:$A$16)-ROW($A$2)+1)),ROWS($G$3:G3)),2))
[/TD]
 [/TR]
[/TABLE]
Excel Workbook
ABCDEFG
1CompanyService NameCategoryCriteriaOutput
2Parliamentary ServiceWAN Service Lite@SILVERNetworkCompanyServices
3Parliamentary ServiceVoice Connect@SILVERVoiceParliamentary ServiceWAN Service Lite@SILVER
4Parliamentary ServiceWAN Service Premium@GOLDNetworkCategoryWAN Service Premium@GOLD
5Parliamentary ServiceWAN Service Delivery Platform@SILVERNetworkNetworkWAN Service Delivery Platform@SILVER
6Parliamentary ServiceWAN Service Premium@SILVERMobileWAN Service Delivery Platform@SILVER
7Parliamentary ServiceWAN Service Delivery Platform@SILVERNetwork
8Parliamentary ServiceVoice Connect@SILVERVoice
9Ministry of JusticeTaaS GNet Site Connectivity@MOJNZXANetwork
10Ministry of JusticeCorporate Mobile Direct@GOLDMobile
11Ministry of JusticeeFax@SILVERVoice
12Ministry of JusticeFrame Relay@GOLDIT
13Ministry of JusticeGen-i Direct Connect@GOLDMobile
14Ministry of JusticeInternet Service Plus@GOLDVoice
15Ministry of JusticeWAN Service Premium@GOLDNetwork
16Ministry of JusticeWAN Service Foundation@GOLDYNetwork
17
18
19
20
21
22
23Ministry of JusticeVoice
24Parliamentary ServiceNetwork
Sheet
 
Upvote 0
Re: If, countif, index help!! :(

joetogo said:
Hi Aladin,

I'd like a list of the "Services" when the Company = MOJ and Category = Network. So the list will be:

TaaS GNet Site Connectivity@MOJNZXA
WAN Service Premium@GOLD
WAN Service Foundation@GOLDY

Company and Category will be dynamic affecting the list content and length as they change. Hope that clarifies?
Click to expand...

What is your Excel version?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Nested IF formula with multiple criteria not working
2
Replies
17
Views
298
CaptainGravyBum
C
M
  • Solved
Countifs not working due to range ? maybe an array?
2
Replies
14
Views
2K
etaf
etaf
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
272
ashleighbrown
A
M
  • Question
Pivot Table help - Can I show certain data depending on value in another field
Replies
2
Views
254
Monty85
M
J
  • Question
mirroring a range of cells from a table based on criteria?
Replies
1
Views
728
JeffGalbraith
J

Forum statistics

Threads
1,214,832
Messages
6,121,851
Members
449,051
Latest member
excelquestion515

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