Excel 2010 COUNTIFS Substitute

[Snipeston]

I have this function/formula in an Excel 2010 spreadsheet that I use:

=COUNTIFS('Prescreen Data'!$E:$E,Predictor!B$3, 'Prescreen Data'!$N:$N,Predictor!$A4)

I need to be able for this spreadsheet to be used by computers running Excel 2003 or later. COUNTIFS does not work in the older versions and I have tried various combinatins of COUNTIF, COUNT, and IF statements and cannot get them to work correctly. Any ideas?

Thanks for any help anyone could offer!

[FDibbins]

countifs() works on 2007, but to suggest an alternative for 2003, I will need to see some sample data

[benishiryo]

hi Snipeston, try:
=SUMPRODUCT(('Prescreen Data'!$E:$E=Predictor!B$3)*('Prescreen Data'!$N:$N=Predictor!$A4))

or don't range up the whole column. maybe:
=SUMPRODUCT(('Prescreen Data'!$E$1000:$E$1000=Predictor!B$3)*('Prescreen Data'!$N$1000:$N$1000=Predictor!$A4))

[Snipeston]

Here is an example. Deleted some of the unnecessary columns and some of the previous cell references have changed.

I need the Predictor sheet to do the following:

1. If the Region Column on the Prescreen Data = the region on the Predictor sheet in B3 (in this case Atlanta)
AND
2. If the Difference Column on the Prescreen Data = the amount in the Predictor sheet in A5
THEN
3. Count how many matches of REGION and AMOUNTs

[Snipeston]

Sorry forgot to add the file...

[Snipeston]

Tried your suggestion, but got a #NUM error

hi Snipeston, try:
=SUMPRODUCT(('Prescreen Data'!$E:$E=Predictor!B$3)*('Prescreen Data'!$N:$N=Predictor!$A4))

or don't range up the whole column. maybe:
=SUMPRODUCT(('Prescreen Data'!$E$1000:$E$1000=Predictor!B$3)*('Prescreen Data'!$N$1000:$N$1000=Predictor!$A4))

[benishiryo]

my 2nd formula should be:
=SUMPRODUCT(('Prescreen Data'!$E$2:$E$1000=Predictor!B$3)*('Prescreen Data'!$N$2:$N$1000=Predictor!$A5))

and you changed A4 to A5.

[Snipeston]

Ok, this seems to be working with one issue...

When I clear the data out of the Prescreen Data sheet, the $E$2 and the $N$2 get replaced by #REF. This does not go away when the new set of data is entered into the Prescreen Data sheet, and have to redo my formula for it to work???

my 2nd formula should be:
=SUMPRODUCT(('Prescreen Data'!$E$2:$E$1000=Predictor!B$3)*('Prescreen Data'!$N$2:$N$1000=Predictor!$A5))

and you changed A4 to A5.

[JosephP]

don't delete the cells-just clear them using the delete key