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
 

Some videos you may like

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
Joined
Jan 14, 2014
Messages
303
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.
 

Lindaland

New Member
Joined
Feb 22, 2019
Messages
3
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
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Name </td><td style=";">Win Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Potter</td><td style="text-align: right;;">1/1/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Weasley</td><td style="text-align: right;;">1/3/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Potter</td><td style="text-align: right;;">2/7/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Granger</td><td style="text-align: right;;">2/13/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Lovegood</td><td style="text-align: right;;">2/1/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Weasley</td><td style="text-align: right;;">2/7/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Weasley</td><td style="text-align: right;;">2/13/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Granger</td><td style="text-align: right;;">3/2/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Potter</td><td style="text-align: right;;">3/1/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Potter</td><td style="text-align: right;;">3/28/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Longbottom </td><td style="text-align: right;;">3/7/2018</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet601</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=IF(<font color="Blue">B5="","",A4</font>)</td></tr></tbody></table></td></tr></table><br />

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
Joined
Jan 14, 2014
Messages
303

ADVERTISEMENT

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
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Name </td><td style=";">Win Date</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Potter</td><td style="text-align: right;;">1/1/2018</td><td style=";">Potter</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Weasley</td><td style="text-align: right;;">1/3/2018</td><td style=";">Weasley</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Potter</td><td style="text-align: right;;">2/7/2018</td><td style=";">Potter</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Granger</td><td style="text-align: right;;">2/13/2018</td><td style=";">Granger</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Lovegood</td><td style="text-align: right;;">2/1/2018</td><td style=";">Lovegood</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Weasley</td><td style="text-align: right;;">2/7/2018</td><td style=";">Weasley</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">2/13/2018</td><td style=";">Weasley</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">3/1/2019</td><td style=";">Weasley</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Granger</td><td style="text-align: right;;">3/2/2018</td><td style=";">Granger</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Potter</td><td style="text-align: right;;">3/1/2018</td><td style=";">Potter</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;">3/28/2018</td><td style=";">Potter</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;">4/1/2019</td><td style=";">Potter</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;">5/1/2019</td><td style=";">Potter</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">Longbottom </td><td style="text-align: right;;">3/7/2018</td><td style=";">Longbottom </td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet601</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">=IF(<font color="Blue">B4,IF(<font color="Red">A4="",C3,A4</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Lindaland

New Member
Joined
Feb 22, 2019
Messages
3
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
Joined
Jun 29, 2014
Messages
5,146
You're welcome, welcome to the forum, and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,373
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top