COUNTIFS formula advice

nunoxx

New Member
Joined
Feb 23, 2020
Messages
2
Office Version
  1. 365
Hello,

I've inherited a spreadsheet at work and have been tasked with maintaining it, though i'm having trouble with a formula.

I have set up four columns for each quarter of the year (Q1, Q2, Q3, Q4), and a column which gets marked as 'Complete' upon completion of a case.

The formulas I currently have to record when a case has been completed in each quarter are:

=COUNTIFS(Quarter_1,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete")
=COUNTIFS(Quarter_2,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete")
=COUNTIFS(Quarter_3,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete")
=COUNTIFS(Quarter_4,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete")

This works fine if data is entered in only one of the quarterly columns for a case. However an issue arises when data is entered into multiple columns for the same case, Q1 and Q3 for example, as it then records the case as having been completed in both Quarter 1 and Quarter 3, when in this example I only want it to update the figure for Quarter 3 as that was when it was officially completed, not Quarter 1.

Is there a way to amend these formula so that it would only update the completion figure for the quarter in which the case was actually completed, and not also for any previous quarters, where they may be data in the column for that case, even though it was not completed in those quarters?

Thanks in advance for any assistance.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
Welcome to Mr Excel :)

The easiest way would be to check the results of the formulas for the later Quarters. Assuming that your 4 formulas are in A2:A5

=IF(AND(A3="",A4="",A5=""),COUNTIFS(Quarter_1,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),"")
=IF(AND(A4="",A5=""),COUNTIFS(Quarter_2,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),"")
=IF(A5="",COUNTIFS(Quarter_3,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),"")
=COUNTIFS(Quarter_4,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete")
 

nunoxx

New Member
Joined
Feb 23, 2020
Messages
2
Office Version
  1. 365
Welcome to Mr Excel :)

The easiest way would be to check the results of the formulas for the later Quarters. Assuming that your 4 formulas are in A2:A5

=IF(AND(A3="",A4="",A5=""),COUNTIFS(Quarter_1,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),"")
=IF(AND(A4="",A5=""),COUNTIFS(Quarter_2,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),"")
=IF(A5="",COUNTIFS(Quarter_3,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),"")
=COUNTIFS(Quarter_4,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete")

Thank you for the above advice.

However when copying the suggested formulas and amending as my formulas are in C2:C5, when I enter data into the reference cells, the cells containing these formula are blank and do not update,
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
so A3 is probably C2
A4 = C3
and A5 = C4

difficult without a good view, use the XL2BB to grab a small dataset to post
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
Oops, I should have used 0, not "". They should work this time...

=IF(AND(C3=0,C4=0,C5=0),COUNTIFS(Quarter_1,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),0)
=IF(AND(C4=0,C5=0),COUNTIFS(Quarter_2,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),0)
=IF(C5=0,COUNTIFS(Quarter_3,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete"),0)
=COUNTIFS(Quarter_4,"<>",'SNP 2020-2021'!$O$3:$O$501,0,'SNP 2020-2021'!$P$3:$P$501,"Complete")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

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
Top