COUNTIFS formula advice

[nunoxx]

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.

 

[jasonb75]

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]

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]

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]

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")