Countif values that are in a different row and column than criteria

[Lindaland]

Hi Everyone,

I am trying find a formula that will count the number of cells that contain a date given a certain criterion. The problem is that the criteria are in a different row and column than some of the cells I need to count. Here is what I'm looking for:



[TABLE="width: 555"]
<tbody>[TR]
[TD="width: 129, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 161, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , colspan: 2"]February Wins
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 94, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]March Wins
[/TD]
[TD="width: 67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]Name
[/TD]
[TD="bgcolor: #E7E6E6"]Win Date
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Potter
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Potter
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Potter
[/TD]
[TD="bgcolor: transparent, align: right"]1/1/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Weasley
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Weasley
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Granger
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Granger
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Weasley
[/TD]
[TD="bgcolor: transparent, align: right"]1/3/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Lovegood
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Lovegood
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Longbottom
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Longbottom
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Potter
[/TD]
[TD="bgcolor: transparent, align: right"]2/7/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Granger
[/TD]
[TD="bgcolor: transparent, align: right"]2/13/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lovegood
[/TD]
[TD="bgcolor: transparent, align: right"]2/1/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Weasley
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2/7/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2/13/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Granger
[/TD]
[TD="bgcolor: transparent, align: right"]3/2/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Potter
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]3/1/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]3/28/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Longbottom
[/TD]
[TD="bgcolor: transparent, align: right"]3/7/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Where the formula determining the number of February and March wins for each person is as follows:

=COUNTIFS($A:$A,E3,$B:$B,">1/31/18",$B:$B,"<3/1/18")

As you can see, Weasley and Potter each had multiple wins in one month, but the formula will only count the first win because the second value is in a different row and column than the criterion in column A. I have tried using offset and a few variations of array formulas to no avail. Any suggestions?

Thanks,
Linda

 

[Col Delane]

Hi Linda, and welcome to the Forum

For this application, and many others in Excel, it is best to layout and populate your lists / tables like true databases - meaning that each row stands on its own and all critical fields are complete. In your case, as far as Excel is concerned, the two people with two wins have their name associated with only one date (the first one listed) - it is only us humans who have the intelligence to quickly interpret the data visually and "assume" that the next date also applies to the person listed in the row above!

So the easy fix is to ensure that all rows are populated with both a Name and Win Date. Doing so also enables proper sorting, filtering and a host of other data summarisation and manipulation techniques.

This answer wasn't along the lines you appeared to be seeking, but I hope it helps anyway.

 

[Lindaland]

Hi Col Delane, thank you for your response and the warm welcome!

I had considered your solution. Unfortunately the actual spreadsheet I’m using consists of several generated reports compiling thousands of rows of data. Modifying the reports would take hours and it would most likely be faster to just count the cells manually. I have contacted our software to provider to customize the report but by the time they complete the modifications, my deadline for submitting the information will have passed.

Can anyone suggest a solution in the form of a formula, pivot table, VBA, or witchcraft that could help with this?

 

[jtakw]

Hi,

You can easily Populate Column A where a player has additional Win Dates in Column B but Blank in corresponding Column A.

Take this sample, and follow instructions below:

Book1
	A	B
1
2	Name	Win Date
3
4	Potter	1/1/2018
5
6	Weasley	1/3/2018
7
8	Potter	2/7/2018
9
10	Granger	2/13/2018
11
12	Lovegood	2/1/2018
13
14	Weasley	2/7/2018
15	Weasley	2/13/2018
16
17	Granger	3/2/2018
18
19	Potter	3/1/2018
20	Potter	3/28/2018
21
22	Longbottom	3/7/2018
Sheet601

Cell Formulas
Range	Formula
A5	=IF(B5="","",A4)

Assuming your data as shown above:

1. Enter formula in A5
2. Right click, Copy
3. Select Column A
4. Hit F5, "Special", choose "Blanks", Click OK
5. Hit Ctrl V

Now All of Column A where it was previously Blank with a corresponding value in B is populated, your COUNTIFS formula will now give you the correct results.

If you don't want this method, you can also opt for a Helper Column.

 

[Col Delane]

After reading Lindaland's response to my solution but before reading jtakw's, the latter's idea is exactly what I thought of - though I would go straight to using a helper column with this formula in C4 and copied down to all rows:
= IF( B4 = "", "-", if( A4 ="", A3, A4 ))

Then use that column as the criteria range in the COUNTIFS function.
To save having to recreate this formula range each time, you could also just have it sitting in a template and paste in the new data from the reports generated by the other software.

 

[jtakw]

If you don't want this method, you can also opt for a Helper Column.

After reading Lindaland's response to my solution but before reading jtakw's, the latter's idea is exactly what I thought of - though I would go straight to using a helper column with this formula in C4 and copied down to all rows:
= IF( B4 = "", "-", if( A4 ="", A3, A4 ))

Then use that column as the criteria range in the COUNTIFS function.
To save having to recreate this formula range each time, you could also just have it sitting in a template and paste in the new data from the reports generated by the other software.

@ Col Delane, your "Helper Column" formula works fine if there are no more than 2 wins per player, otherwise, it'll error out.

@ OP, if you want to opt for a "Helper Column", for the following example, in C4 copied down:

Book1
	A	B	C
1
2	Name	Win Date
3
4	Potter	1/1/2018	Potter
5
6	Weasley	1/3/2018	Weasley
7
8	Potter	2/7/2018	Potter
9
10	Granger	2/13/2018	Granger
11
12	Lovegood	2/1/2018	Lovegood
13
14	Weasley	2/7/2018	Weasley
15		2/13/2018	Weasley
16		3/1/2019	Weasley
17
18	Granger	3/2/2018	Granger
19
20	Potter	3/1/2018	Potter
21		3/28/2018	Potter
22		4/1/2019	Potter
23		5/1/2019	Potter
24	Longbottom	3/7/2018	Longbottom
Sheet601

Cell Formulas
Range	Formula
C4	=IF(B4,IF(A4="",C3,A4),"")

 

[Lindaland]

Thank you for your wonderful suggestions! The helper column does exactly what I need, and I can use the template for future reports without re-entering the formula. Brilliant!

 

[jtakw]

You're welcome, welcome to the forum, and thanks for the feedback.