MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP - My data analysis project - how to do .... this?


Posted by Dixon on October 11, 2001 9:16 AM

OK, I'm analyzing many excel files in the same format/layout and need a function or VBA code that will do this for me:

A variant of the COUNTIF function that allows multiple criteria. What I need is, out of a list of 3000-6000 payments, how many are either $400.00 or evenly divisible by $400... using a modolus operator would be great... but I cant just do COUNTIF(myrange,400 or this.cell % 400 = 0)...

Any help? Thanks.

So, to reiterate, I have a range (D2:D6000) and need to count up how many values are either 400, or evenly divisible by 400.


Posted by Aladin Akyurek on October 11, 2001 9:49 AM

Try:

=SUMPRODUCT((ISNUMBER(A1:A20)*(MOD(A1:A20,400)=0)))

Adjust the range to fit your situation.

Aladin

========