Index & Match with counta

[tom_blamire]

Hi,

I have a column that i need to count (cells contain text or numbers or both) but based on 4 criteria. I tried using the index and match based on 4 criteria with a counta element but was returning both #REF and #N/A. Example of formula used was:

=INDEX(COUNTA(DATA!$T:$T),MATCH('PIVOT TABLES'!$MI$1&'PIVOT TABLES'!$MI$2&'PIVOT TABLES'!$MI$3&'PIVOT TABLES'!MH7,NEWRENEWAL&YEAR&MONTH&SALESPERSON,0))


Can anyone help?

Tom

 

[MFitz10]

Try a Countifs

=Countifs(NewRenewal,PIVOT TABLES'!$MI$1,Year,PIVOT TABLES'!$MI$2,Month,PIVOT TABLES'!$MI$3,SalesPerson,PIVOT TABLES'!$MI$4)