COUNTIF AND COUNTIF

[Dave]

I have 2 conditions in 2 different ranges I want to compare

column A is last day
column B is sales

I want to count how many customers have not been in in 365 days and spent
more than 100 dollars
I tried =countif(a:a,">365") and countif(b:b,">100")
it's not working and thoughts

 

[PCLIVE]

Try SUMPRODUCT:

=SUMPRODUCT(--(A1:A100>365),--(B1:B100>100))

Note, you must specify the range. Adjust as needed.

HTH,
Paul

 

[Sean Timmons]

Can't use 2 countif's like that. In 2007, there is a formula called countifs.
If you don't have that, use SUMPRODUCT

=SUMPRODUCT(--($A$2:$A$5000>365),--($B$2$B$5000>100))

Will get it for you.

You cannot use A:A in sumproduct. You can do A1:A65536 if you need to...

 

[Ashish Mathur]

Hi,

You could also try this array formula (Ctrl+Shift+Enter)

=sum(if((range>365)*(range2>100),1))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com