Using COUNTIFS with INDIRECT trying to use multiple criteria...Not working.

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
Hello,

So, I am using the following COUNTIFS formula with INDIRECT to populate the small table (on the left of my dashboard) by looking up which worksheet (Shipping Week) is selected in C5 along with which warehouse is selected in C6 (Column B in the table on the selected worksheet (2nd uploaded imageCapture1.jpg)) as an example of a partial sheet, showing both below…I use this formula to count the number of times the column for “On Time” shipments (Column F) is “0” for late. I do almost the same formula to find the number of times the shipments were “On Time” by replacing the “1” in the formula with a “0”. I then add them together to get the total number of shipments (Rows) in the small table I have on the charts dashboard (uploaded image "Capture.jpg". )

=COUNTIFS(INDIRECT(C5 & "!F:F"),1,INDIRECT(C5 & "!B:B"),C6)

=COUNTIFS(INDIRECT(C5 & "!F:F"),0,INDIRECT(C5 & "!B:B"),C6)

This part works fine...

Column G on the selected worksheet is a (Data Vaildation) list of the reason codes, built directly from the list in the small dashboard table (B8:B11). The numbers generated here are charted to the right of the small dashboard table. For that, I….was…using (For the example of C13):

=COUNTIF(INDIRECT("'"&C5&"'!G:G"),"Components - Quality Issue")

To specifically count the number of times this "Reason Code" occurred in the late shipments...

However, this formula is omitting the reference to the second drop down list in C6, so the charts are being populated only based on the “OnTime” reference entry, not filtering the data by way of the selected warehouse, so the charts always show only the complete totals for the whole worksheet selected. I wish to replace this formula with something similar to:

=COUNTIFS(INDIRECT(C5 & "!F:F"),1,INDIRECT(C5 & "!B:B"),C6,INDIRECT(C6 & "!G:G"),"Components - Quality Issue")

# of times the shipments were late on the given worksheet - Filtered by the selected warehouse in C6 (Column B on the worksheet.), - and again filtered, to only show if the reason code in column G of the worksheet matches the text at the end and the B cell on the dashboard data table.

This formula keeps failing for too many arguments and I can’t seem to get the syntax to work.

I need the two drop down references AND the match to the reason code to all COMBINE for the chart to populate correctly every time I change worksheets in the first drop down list and I change the warehouse in the 2nd drop down list.

I appreciate any help anyone can provide. I'm driving my self nuts changing the syntax many times trying to get better results but I just keep getting errors....
 

Attachments

  • Capture.JPG
    Capture.JPG
    42.6 KB · Views: 13
  • Capture1.JPG
    Capture1.JPG
    48.4 KB · Views: 13

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,258
Office Version
  1. 365
Platform
  1. Windows
Shouldn't that be C5 in the Indirect function for col G?
 

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
I did that and no errors but that brings me back to something not counting right when I can look at the actual worksheet and see that there are 7 entries for that specific "Reason code" and it returns "0". Then I modified the formula to this:

=COUNTIFS(INDIRECT(C5 & "!F:F"),1,INDIRECT(C5 & "!B:B"),C6,INDIRECT(C5 & "!G:G"),B13)

And it still shows "0" as a result. Should be 7
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,258
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If this formula is giving the correct result
Excel Formula:
=COUNTIFS(INDIRECT(C5 & "!F:F"),1,INDIRECT(C5 & "!B:B"),C6)
That suggests that either col G doesn't exactly match B13, or there are no rows that meet all three criteria.
 

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
This worked...I added the extra Indirect reference back to the list of Reasons on the main page and changed the "1" for true to the first argument to a "0" for false, or, not on time:

=COUNTIFS(INDIRECT(C5 & "!F:F"),0,INDIRECT(C5 & "!B:B"),C6,INDIRECT(C5 & "!G:G"),B13)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,258
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,026
Messages
5,622,280
Members
415,890
Latest member
macak333

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