Countif and range values

Hello,

Could someone please help!

I posted an earlier for thread about countif which has been solved but would appreciate some help with something more complex now!

I am doing a number count for open tickets, a ticket is titled as

[1] Development::Futures Dev

so i am searching for this on another sheet and i know the range but for criteria i would like to only count cells which

a) match the exact '[1] Development::Futures Dev'
b) have blank space in the column next to it which means the ticket is still open.

Many Thanks,

S

Hi there,

Thank you very much.

Please see my sheet attached.

In Sheet1 Cell B2 i have my countif formula. But to count open tickets I need the formula to look at the Alldata tab under column C and make sure that the information matches the data in the the cells e.g. Sheet1 cellA2= cellC2 Alldata tab and then there is no close time in cell B2 Alldata tab so this is still open and would count as 1 open ticket.

I have tried using =countif and specifying the range and then for criteria putting in an =if formula but from here it gets beyond my knowledge and i am not sure how to incorporate so many 'if' criteria as i keep getting formula errors.

Thank you

S

That's awesome! Thank you very much!

Pepe,

can i ask one last thing please.

Your formula works when there is a blank

=SUMPRODUCT((AllData!G10:G1234=Summary!A5)*(AllData!F10:F1234=""))

and also when i populate the last part with "open" e.g.

=SUMPRODUCT((AllData!G10:G1234=Summary!A5)*(AllData!F10:F1234="open"))

But how do i ask the formula to look for more than one item e.g. "open" & "new"

I've tried the below but they don't work:

=SUMPRODUCT((AllData!G10:G1234=Summary!A5)*(AllData!F10:F1234="open","new"))

&

=SUMPRODUCT((AllData!G10:G1234=Summary!A5)*(AllData!F10:F1234="open,new")

Do you knwo where i am going wrong please?

Many Thanks,

S