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

#### Lindaland

##### New Member
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Col Delane

##### Active Member
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

##### New Member
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

##### Well-known Member
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:

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:

#### Col Delane

##### Active Member

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

##### Well-known Member
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:

#### Lindaland

##### New Member
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

##### Well-known Member
You're welcome, welcome to the forum, and thanks for the feedback.

Replies
13
Views
699
Replies
4
Views
56
Replies
4
Views
382
Replies
1
Views
58
Replies
1
Views
239