Counting based on 2 Arrays with Null Values

0
votes

I have two arrays of data I am trying to count. One array is all text(Source column) and the other is all dates (date ticket closed) represented as 1-Jan for January 1st.

What I am trying to do is count how many containing a source, but no date (signifying that the ticket is still open).

I have tried many different formulas and each one returns an error. I think the 2 closed I've got are:

(I also had one that replaced COUNTA with both COUNTIF and it still did not work properly.)

=COUNTIF('Help Desk Log'!B2:B, "Internal") - COUNTIF('Help Desk Log'!K2:K,"="&" " )

and 

=IF(AND('Help Desk Log'!B2:B = "Internal", 'Help Desk Log'!K2:K - " "), COUNTA('Help Desk Log'!J2:J) - COUNTA(ISTEXT('Help Desk Log'!K2:K)))


 Source            Date
External        1-Jan
Internal        (Null)
Internal        1-Jan
External        2-Jan
Internal        2-Jan
Internal        (Null)
Internal        (Null)
Internal        4-Jan

Both return a #NAME? error.

I am not sure what to change to make it work.

1
arraysexcelexcel-formula

1 Answers

1
votes

Use COUNTIFS():

=COUNTIFS('Help Desk Log'!B:B, "Internal",'Help Desk Log'!K:K,"=")