COUNTIF formula difficulty using multiple worksheets

[mattbird]

I am using a COUNTIF formula to pull information from a worksheet. Depending on what is selected in two lists.

List 1 contains (cell G5): All Qtrs, Q1, Q2, Q3 and Q4
List 2 contains (cell G4): All Reports, R1, R2, R3 and R4

The formula I am using at present works, as the data is on the same worksheet:

=COUNTIFS('Report Schedule 2023-24'!$A$3:$A 234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="AIl Reports","?*",$G$4),'Report Schedule 2023-24'!SH$3:SH234,B7,'Report Schedule 2023-24'SPS3:SP284"<>")

The difficulty I am having is adjusting the formula, as I want it to pull data from another worksheet called ‘Audit Review’ when R2 is selected in list 2. List 1 will also need to work with this worksheet.

The Audit Review worksheet is a duplicate of the Report Schedule worksheet, cell have same information criteria etc.

How do I adjust the formula to do this?

I hope this make sense, as it difficult to explain.

Thanks Matt

 

[DanteAmor]

Your formula has errors. It's hard to help you when your own formula has errors:

The difficulty I am having is adjusting the formula, as I want it to pull data from another worksheet called ‘Audit Review’ when R2 is selected in list 2

But you don't mention what you want to add or what you need to pull or count.
The sheet is instead of?

Try:
Since I don't understand what change you need I'm going to venture something like this:

=IF($G$4="R2",COUNTIFS(
'Audit Review'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Audit Review'!$E$3:$E234,IF($G$4="AIl Reports","?*",$G$4),
'Audit Review'!SH$3:SH234,B7,'Audit Review'!SP3:SP234,"<>"),
COUNTIFS(
'Report Schedule 2023-24'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="AIl Reports","?*",$G$4),
'Report Schedule 2023-24'!SH$3:SH234,B7,'Report Schedule 2023-24'!SP3:SP234,"<>"))

 

[mattbird]

Hi,

Firstly thanks for your help and I apologies for the formula being wrong, it didn't copy across correctly for some reason!

Any way I have amended the formula and placed in the correct data ranges etc:

=IF($G$4="R2",COUNTIFS('Audit Review 2023-24'!$A$3:$A81,IF($G$5="All Qtrs","?*",$G$5),'Audit Review 2023-24'!$E$3:$E81,IF($G$4="All Reports","?*",$G$4),'Audit Review 2023-24'!$H$3:$H81,B7,'Audit Review 2023-24'!$Q$3:$Q81,"<>"),COUNTIFS('Report Schedule 2023-24'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="All Reports","?*",$G$4),'Report Schedule 2023-24'!$H$3:$H234,B7,'Report Schedule 2023-24'!$P$3:$P234,"<>"))

The formula is partially working.

List 1 contains (cell G5): All Qtrs, Q1, Q2, Q3 and Q4
List 2 contains (cell G4): All Reports, R1, R2, R3 and R4

If I select either R1,R3 or R4 from list 2 (cell G4) it returns the data I require from the 'Report Schedule' worksheet and List 1 (Cell G5) options work with it.

and if I select R2 from list 2 (cell G4) it returns the data I require from the 'Audit Review' worksheet and List 1 (Cell G5) options work with it to, which is great.

However, if I select 'All Reports' from list 2 (cell G4) it is only returning the data from the 'Report Schedule' worksheet, but I would like it to return the data from both worksheets.

Can this be done?

Thanks Matt

 

[DanteAmor]

However, if I select 'All Reports' from list 2 (cell G4) it is only returning the data from the 'Report Schedule' worksheet, but I would like it to return the data from both worksheets.

Try:

=IF($G$4="R2",COUNTIFS(
'Audit Review'!$A$3:$A81,IF($G$5="All Qtrs","?*",$G$5),'Audit Review'!$E$3:$E81,$G$4,
'Audit Review'!$H$3:$H81,B7,'Audit Review'!$Q$3:$Q81,"<>"),
COUNTIFS(
'Report Schedule 2023-24'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="All Reports","?*",$G$4),
'Report Schedule 2023-24'!$H$3:$H234,B7,'Report Schedule 2023-24'!$P$3:$P234,"<>") +
IF($G$4="All Reports",COUNTIFS(
'Audit Review'!$A$3:$A81,IF($G$5="All Qtrs","?*",$G$5),'Audit Review'!$E$3:$E81,"?*",
'Audit Review'!$H$3:$H81,B7,'Audit Review'!$Q$3:$Q81,"<>")))

It is a very big formula. I recommend using name ranges for each column of your Audit Review and Report Schedule 2023-24 sheets.
For example, something like this:

=IF($G$4="R2",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,$G$4,AR_H,B7,AR_Q,"<>"),
COUNTIFS(
'Report Schedule 2023-24'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="All Reports","?*",$G$4),
'Report Schedule 2023-24'!$H$3:$H234,B7,'Report Schedule 2023-24'!$P$3:$P234,"<>") +
IF($G$4="All Reports",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,"?*",AR_H,B7,AR_Q,"<>")))

 

[DanteAmor]

Here the version with named range in both sheets:

=IF($G$4="R2",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,$G$4,AR_H,B7,AR_Q,"<>"),
COUNTIFS(RS_A,IF($G$5="All Qtrs","?*",$G$5),RS_E,IF($G$4="All Reports","?*",$G$4),RS_H,B7,RS_P,"<>") +
IF($G$4="All Reports",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,"?*",AR_H,B7,AR_Q,"<>")))

Example of a named range:

Repeat steps 1 to 3 for each column of each sheet with the names of the example that I put in the formula.

 

[mattbird]

Here the version with named range in both sheets:

=IF($G$4="R2",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,$G$4,AR_H,B7,AR_Q,"<>"),
COUNTIFS(RS_A,IF($G$5="All Qtrs","?*",$G$5),RS_E,IF($G$4="All Reports","?*",$G$4),RS_H,B7,RS_P,"<>") +
IF($G$4="All Reports",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,"?*",AR_H,B7,AR_Q,"<>")))

Example of a named range:
View attachment 92672

Repeat steps 1 to 3 for each column of each sheet with the names of the example that I put in the f

Try:

=IF($G$4="R2",COUNTIFS(
'Audit Review'!$A$3:$A81,IF($G$5="All Qtrs","?*",$G$5),'Audit Review'!$E$3:$E81,$G$4,
'Audit Review'!$H$3:$H81,B7,'Audit Review'!$Q$3:$Q81,"<>"),
COUNTIFS(
'Report Schedule 2023-24'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="All Reports","?*",$G$4),
'Report Schedule 2023-24'!$H$3:$H234,B7,'Report Schedule 2023-24'!$P$3:$P234,"<>") +
IF($G$4="All Reports",COUNTIFS(
'Audit Review'!$A$3:$A81,IF($G$5="All Qtrs","?*",$G$5),'Audit Review'!$E$3:$E81,"?*",
'Audit Review'!$H$3:$H81,B7,'Audit Review'!$Q$3:$Q81,"<>")))

It is a very big formula. I recommend using name ranges for each column of your Audit Review and Report Schedule 2023-24 sheets.
For example, something like this:

=IF($G$4="R2",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,$G$4,AR_H,B7,AR_Q,"<>"),
COUNTIFS(
'Report Schedule 2023-24'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="All Reports","?*",$G$4),
'Report Schedule 2023-24'!$H$3:$H234,B7,'Report Schedule 2023-24'!$P$3:$P234,"<>") +
IF($G$4="All Reports",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,"?*",AR_H,B7,AR_Q,"<>")))

Hi,

Here the version with named range in both sheets:

=IF($G$4="R2",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,$G$4,AR_H,B7,AR_Q,"<>"),
COUNTIFS(RS_A,IF($G$5="All Qtrs","?*",$G$5),RS_E,IF($G$4="All Reports","?*",$G$4),RS_H,B7,RS_P,"<>") +
IF($G$4="All Reports",COUNTIFS(AR_A,IF($G$5="All Qtrs","?*",$G$5),AR_E,"?*",AR_H,B7,AR_Q,"<>")))

Example of a named range:
View attachment 92672

Repeat steps 1 to 3 for each column of each sheet with the names of the example that I put in the formula.

Hi,

I have tried the first example (large formula), however it opens a window called "Update Values: Audit Review"??

The second example (small formula), works as far as I can tell, I will have a play around hopefully tomorrow and will let you know the outcome.

Is there any down fall to using name ranges?

Thanks again.

 

[DanteAmor]

have tried the first example (large formula), however it opens a window called "Update Values: Audit Review"??

Update the sheet name in the formula:
=IF($G$4="R2",COUNTIFS(
'Audit Review 2023-24'!$A$3:$A81,IF($G$5="All Qtrs","?*",$G$5),'Audit Review 2023-24'!$E$3:$E81,$G$4,
'Audit Review 2023-24'!$H$3:$H81,B7,'Audit Review 2023-24'!$Q$3:$Q81,"<>"),
COUNTIFS(
'Report Schedule 2023-24'!$A$3:$A234,IF($G$5="All Qtrs","?*",$G$5),'Report Schedule 2023-24'!$E$3:$E234,IF($G$4="All Reports","?*",$G$4),
'Report Schedule 2023-24'!$H$3:$H234,B7,'Report Schedule 2023-24'!$P$3:$P234,"<>") +
IF($G$4="All Reports",COUNTIFS(
'Audit Review 2023-24'!$A$3:$A81,IF($G$5="All Qtrs","?*",$G$5),'Audit Review 2023-24'!$E$3:$E81,"?*",
'Audit Review 2023-24'!$H$3:$H81,B7,'Audit Review 2023-24'!$Q$3:$Q81,"<>")))

Is there any down fall to using name ranges?

Not in this case. And it is a great help to reduce the formula.