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

Lindaland

New Member
Joined
Feb 22, 2019
Messages
3
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:



February Wins
March Wins
Name
Win Date
Potter
1
Potter
1
Potter
1/1/2018
Weasley
1
Weasley
0
Granger
1
Granger
1
Weasley
1/3/2018
Lovegood
1
Lovegood
0
Longbottom
0
Longbottom
1
Potter
2/7/2018
Granger
2/13/2018
Lovegood
2/1/2018
Weasley
2/7/2018
2/13/2018
Granger
3/2/2018
Potter
3/1/2018
3/28/2018
Longbottom
3/7/2018

<tbody>
</tbody>


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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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 :ROFLMAO: 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.
 
Upvote 0
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?
 
Upvote 0
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
AB
1
2NameWin Date
3
4Potter1/1/2018
5 
6Weasley1/3/2018
7
8Potter2/7/2018
9
10Granger2/13/2018
11
12Lovegood2/1/2018
13
14Weasley2/7/2018
15Weasley2/13/2018
16
17Granger3/2/2018
18
19Potter3/1/2018
20Potter3/28/2018
21
22Longbottom3/7/2018
Sheet601
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
ABC
1
2NameWin Date
3
4Potter1/1/2018Potter
5
6Weasley1/3/2018Weasley
7
8Potter2/7/2018Potter
9
10Granger2/13/2018Granger
11
12Lovegood2/1/2018Lovegood
13
14Weasley2/7/2018Weasley
152/13/2018Weasley
163/1/2019Weasley
17
18Granger3/2/2018Granger
19
20Potter3/1/2018Potter
213/28/2018Potter
224/1/2019Potter
235/1/2019Potter
24Longbottom3/7/2018Longbottom
Sheet601
Cell Formulas
RangeFormula
C4=IF(B4,IF(A4="",C3,A4),"")
 
Upvote 0
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! :)
 
Upvote 0
You're welcome, welcome to the forum, and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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