Using CountIf Using Variable Rows

[bhinojosa]

I am trying to use CountIf, but making the Range using a formula to find a row in another sheet. It is a little bit complex and I haven't found any examples or advice on how to do this correctly on any tutorials. I am hoping someone here could help me figure this out.

Here are the two sheets and what I am trying to do exactly:


SHEET 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]11-Apr-2019
[/TD]
[TD]18-Apr-2019
[/TD]
[TD]25-Apr-2019
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


SHEET 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]Bob
[/TD]
[TD]11-Apr-2019
[/TD]
[TD]11-Apr-2019
[/TD]
[TD]25-Apr-2019
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In sheet 1 (cell B2) I want a formula that will look for the Row in Sheet 2 that starts with "Bob". Then count all the cells in that row which contain "11-Apr-2019" (i.e., that match Cell B1).

Cell C2 would look up the row in Sheet 2 with "Bob" and count all the cells in that row with "18-Apr-2019" (i.e., that match C1).

Sheet 2 will be sorted from time to time, so the formula needs to look up the name to find the correct Row, it cannot be based off the row number.

So, the question is; how do I make a Range in CountIf that is based off the name in Column A in Sheet 1. or is there another function alltogether I should be using.

 

[CyrusTheVirus]

Welcome to the forums. Maybe try something like the below. Please test it (sorting etc.) before implementing.

Cell B2 (copied down and over):

=COUNTIF(INDEX(Sheet2!$B:$XFD,MATCH($A2,Sheet2!$A:$A,0),),B$1)

 

[bhinojosa]

Thank you! That worked exactly! Just before your answer I worked out a really funky INDIRECT formula that did the trick, except it took 1 minute to caluclate. With your formula its instant.

Do you mind explaining how it works. Sorry I'm a novice, but trying to learn as much as possible.

Thanks again.

 

[CyrusTheVirus]

Sure thing. Just be careful using volatile functions like INDIRECT since they recalculate every time you change data on the spreadsheet.

With that said, the reason my formula works quickly is because it first does a lookup (MATCH) to find out which row to pull. The MATCH tells INDEX which ROW to deliver to COUNTIF, but the trick is to put the 'comma' at this part >

[COLOR=#333333]),)[/COLOR]

in between the parenthesis to tell INDEX not to isolate a certain column within the row, but instead to pull ALL columns for the given delivered row...

That last part might be a bit confusing, but give it some thought and maybe experiment a bit and it might make sense.

Reach out with any other questions. Hope this helped.