Help with Count Formula

[s_k_s]

Hi,


I have 2 columns, one with the Ext number (e.g 3201) and another with type of telephone. At the moment I had a formula =SUMPRODUCT((Telephony!H10:H145={"Analogue Phones"})+0)to just count the telephones but now some people have put the ext as "broken" instead of putting their ext number in so I need to exclude this from the count.


How do I amend the above formula so that it counts the phones that have the extension agaonst it but excludes those phones from the count that have Broken in the extension column?


Your help would be greatly appreciated.


Thanks in advance,


SKS

 

[Hui]

s_K_s


Something like:


=SUMPRODUCT((Telephony!H10:H145={"Analogue Phones"})*(Telephony!ExtColumn<>"")) - SUMPRODUCT((Telephony!H10:H145={"Analogue Phones"})*(Telephony!ExtColumn="Broken"))


Adjust columns to suit

 

[s_k_s]

Hi Hui,


Apologies but having trouble with adjusting colums to suit. The extension data is in the same Telephony tab and in column C in the same range C10:C145. What would the formula be in that case?


Regards,


SKS

 

[Hui]

Can you post a sample file please

 

[Colin Legg]

Provided you are using Excel 2007 or later, I think you can do this with COUNTIFS() which is simpler than SUMPRODUCT().

[pre]

=COUNTIFS(Telephony!H10:H145,"Analogue Phones",Telephony!C10:C145,"<>",Telephony!C10:C145,"<>Broken")

[/pre]
So that formula says count H10:H145 where the value is Analogue Phones but only when C10:C145 isn't empty and doesn't contain the word Broken.

 

[s_k_s]

Hi Hiu,


Sorry, took a while to get everything set up to share the file. The link is: http://db.tt/QtiltPYd


Thanks


SKS

 

[Faseeh]

Hi S_k_s,


This is Collin's formula adjusted to your sheet:

=COUNTIFS(H10:H64,"Analogue Phones",C10:C64,"<>Broken")

Regards,

 

[s_k_s]

Thanks very much for your help. Much appreciated.