Countifs using cross sheet references

Sharon Castiglia

Hello,

I am trying to get a count of items with these Criteria

Stream = XXX

Window Start <>""

Survey Status = ""

Assignment Status = 

 Vendor Assigned

 Vendor Scheduling

 Late - requested update

This is the current formula I have which is populating a quantity of 0 (Actual count should be 325.

=COUNTIFS({Site Survey Stream}, $STREAM$1, {Window Start}, <>"", {Survey Status}, "", {Assignment Status}, "Vendor Assigned", {Assignment Status}, "Vendor Scheduling", {Assignment Status}, "Late - requested update")

Any help certainly would be very appreciated.

Sharon C.

Paul Newcome

This is the problem:

{Assignment Status}, "Vendor Assigned", {Assignment Status}, "Vendor Scheduling", {Assignment Status}, "Late - requested update"

Having range/criteria/range/criteria, AND is implied which means that triggering a count would mean the cell was equal to all 3 of those things at the same time which isn't possible. Try using an OR statement like so:

=COUNTIFS({Site Survey Stream}, $STREAM$1, {Window Start}, <>"", {Survey Status}, "", {Assignment Status}, OR(@cell = "Vendor Assigned", @cell = "Vendor Scheduling", @cell = "Late - requested update"))

RossL

@Paul Newcome

you beat me to it nice answer

Paul Newcome

Make sure your formula is mimicking your filter. Your current formula currently states the the {Survey Review Date} must be greater than or equal to 6 Sept. AND 12 Sept. at the same time. I imagine one of those is probably supposed to be a less than.

Paul Newcome

This is the problem:

{Assignment Status}, "Vendor Assigned", {Assignment Status}, "Vendor Scheduling", {Assignment Status}, "Late - requested update"

Having range/criteria/range/criteria, AND is implied which means that triggering a count would mean the cell was equal to all 3 of those things at the same time which isn't possible. Try using an OR statement like so:

=COUNTIFS({Site Survey Stream}, $STREAM$1, {Window Start}, <>"", {Survey Status}, "", {Assignment Status}, OR(@cell = "Vendor Assigned", @cell = "Vendor Scheduling", @cell = "Late - requested update"))

RossL

@Paul Newcome

you beat me to it nice answer

Sharon Castiglia

One more Question:

Once again Mr. Newcome, you are Johnny on the Spot. Thank you so much. I have another issue:

Have the following formula:

=COUNTIFS({Site Survey Stream}, $STREAM$14, {Survey Status}, "Green", {Survey Reviewed Date}, >=(DATE(2020, 9, 6)), {Survey Reviewed Date}, >=(DATE(2020, 9, 12)))

Coming up with a sum of 0, but when I apply the filter on the source sheet it is showing a quantity of 7, what am I doing wrong?

Thank you

Sharon Castiglia

Oh and the total was correct without drilling down to the date range.

Paul Newcome

Make sure your formula is mimicking your filter. Your current formula currently states the the {Survey Review Date} must be greater than or equal to 6 Sept. AND 12 Sept. at the same time. I imagine one of those is probably supposed to be a less than.

Sharon Castiglia

Thank you so much. I was looking at that formula way too long and couldn't see the obvious. Fresh eyes make all the difference

Paul Newcome

Happy to help. 👍️