How to Exclude Certain Parameters

[EfficientOne]

Hello,
Am attempting to report tickets that are currently open and were created within a certain time range. The problem arises when I attempt to eliminate items that were opened and closed within the same date range.

The current formula is =COUNTIF('name of tab'!$B$!:$B$100, "1") where 1 equals Critical, 2 equals High, 3 equals Medium, etc

This formula returns everything and I only want to show those with an open status (i.e Acknowledged, Open, In Progress, etc.) and not those with a status of Resolved or Closed.

Columns -
B: Priority
K: Status

What do I need to add for the formula to count how many of each priority are in each status (combined)?

Thank you.

 

[VoG]

Try

=SUMPRODUCT(--(B1:B100=1),--(K1:K100="Open"))

 

[EfficientOne]

Thank you for responding. The formula is on a different tab than the data.

1. Do I place 'name of tab' in the same format as the original example?
2. Would I enter a separate formula for all of the categories that are considered 'open' i.e., acknowledge, in progress)?
3. Would it be simpler to have the formula exclude both Resolved and Closed?

 

[VoG]

It would be

=SUMPRODUCT(--(Sheet1!B1:B100=1),--(Sheet1!K1:K100="Open"))

You would need a separate formula for each combination. A Pivot Table might be better - http://www.contextures.com/tiptech.html

 

[EfficientOne]

This is what I typed and it gives me a result of - (The column is formatted for accounting with no decimals.)

=SUMPRODUCT(--'detail'!B1:B7000=1),--('detail'!K1:K7000="Open"),--('detail'!K1:K7000="Acknowledged"),--('detail'!K1:K7000="In Progress"),--('detail'!K1:K7000="Awaiting End User Response"))

I've clicked both ENTER at the end and CNTRL+SHIFT+ENTER.

When I click on the array and Fx it shows the first array with a result of lots of zeros but the others have zeros and ones.

 

[VoG]

Try

=SUMPRODUCT(--(Detail!B1:B7000=1),--ISNUMBER(MATCH(Detail!K1:K7000,{"Open","Acknowledged","In Progress","Awaiting End User Response"},0)))

 

[EfficientOne]

VoG,
Thisone worked like a charm! You are awesome!!!

When I was updating all of the fields, I found that one of the B parameters (Detail!B1:B7000=Request) is text and not just a number so it didn't work. What do I need to change to make that one work?

=SUMPRODUCT(--(Detail!B1:B7000=1),--ISNUMBER(MATCH(Detail!K1:K7000,{"Open","Acknowledged","In Progress","Awaiting End User Response"},0)))

 

[VoG]

Try

=SUMPRODUCT(--(Detail!B1:B7000="Request"),--ISNUMBER(MATCH(Detail!K1:K7000,{"Open","Acknowledged","In Progress","Awaiting End User Response"},0)))

 

[EfficientOne]

Good Morning. De ja vu.

This is what I typed and it gives me a result of - (The column is formatted for accounting with no decimals.) The column with the detail is formatted as text.

=SUMPRODUCT(--'detail'!B1:B7000="Request"),--ISNUMBER(MATCH('detail'!K1:K7000,{"Open","Acknowledged","In Progress","Awaiting End User Response"},0)))

I've clicked both ENTER at the end and CNTRL+SHIFT+ENTER.

 

[VoG]

That should be

=SUMPRODUCT(--(detail!B1:B7000="Request"),--ISNUMBER(MATCH(detail!K1:K7000,{"Open","Acknowledged","In Progress","Awaiting End User Response"},0)))

CTRL + Shift + Enter is not required.