COUNTIFS with No Criteria

[Lawrence]

Hi all, I'm hoping you can help...

In column A I have 700 customer names, in column B they all have a 'sector' (public, corporate or commercial) and in column C they all have a date (Jan-14 to Dec-14).

I have then set up a table that has the months along the top and the sectors down the side and I need a formula to count how many customers are in each sector in each month.

I thought a COUNTIFS would work but I don't need the first range to have a criteria. I just want it to count every time there is a customer name.

Could anyone kindly help?

Hope that makes sense!

Thanks

 

[Claus Busch]

Hi,

Am Tue, 4 Feb 2014 06:39:29 -0800 (PST) schrieb Lawrence:

In column A I have 700 customer names, in column B they all have a 'sector' (public, corporate or commercial) and in column C they all have a date (Jan-14 to Dec-14).

I have then set up a table that has the months along the top and the sectors down the side and I need a formula to count how many customers are in each sector in each month.

if your months are in row 1 with full name e.g. "January" try in B2:
=SUMPRODUCT(--(TEXT(Sheet1!$C$1:$C$700,"MMMM")=B$1),--(Sheet1!$B$1:$B$700=$A2),--(Sheet1!$A$1:$A$700<>""))


Regards
Claus B.