#### nunoxx

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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### jasonb75

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

Replies
19
Views
369
Replies
4
Views
47
Replies
10
Views
188
Replies
24
Views
255
Replies
2
Views
90

1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

### 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.

### Which adblocker are you using?

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

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