Can I combine CountIf with IF or AND?

[Gmolatore]

Can I make the CountIf function conditional by preceding it with IF as follows:
=IF(O8:O264

0,(CountIF(M8:M264,"Issued",N8:N264)+CountIf(M8:M264,"Delivered",N8:N264))

This formula doesn't work, but you can see what I'm trying to do.
Alternatively to do the same thing using AND:
CountIF(AND((M8:M264,"Issued",O8:O264

0),N8:N264)+CountIf(AND((M8:M264,"Delivered",O8:O264 >0),N8:N264))

This also doesn't work. Any tips?

 

[T. Valko]

Are you wanting a count or a sum?

Here's the sum:

=SUMPRODUCT((M8:M264="Issued")+(M8:M264="Delivered"),--(O8:O264>0),N8:N264)

Here's the count:

=SUMPRODUCT((M8:M264="Issued")+(M8:M264="Delivered"),--(O8:O264>0))

 

[Gmolatore]

What's wrong with this?
=sumproduct(('Old Mutual'!$L$7:'Old Mutual'!$L$263,"=Issued")+('Old
Mutual'!$L$7:'Old Mutual'!$L$263,"=Delivered"),--('Old Mutual'!O7:'Old
Mutual'!O263=0))

 

[T. Valko]

Try it like this:

=SUMPRODUCT(('Old Mutual'!$L$7:$L$263="Issued")+('Old
Mutual'!$L$7:$L$263="Delivered"),--('Old Mutual'!$O$7:$O$263=0))

Note that empty cells will evaluate to =0. So you might have to add another
test:

=SUMPRODUCT(('Old Mutual'!$L$7:$L$263="Issued")+('Old
Mutual'!$L$7:$L$263="Delivered"),--(ISNUMBER('Old
Mutual'!$O$7:$O$263)),--('Old Mutual'!$O$7:$O$263=0))

 

[Gmolatore]

I get an Invalid Cell Reference warning when I use your formulas below.
Basically, I want the Countif functions to execute if the cells in Column O
are blank. Something like this (which doesn't work) =COUNTIF('Old
Mutual'!$L$7:'Old Mutual'!$L$263,"=Issued")+COUNTIF('Old Mutual'!$L$7:'Old
Mutual'!$L$263,"=Delivered")+ISBLANK('Old
Mutual'!$O$7:$O$263) For some reason, the CountIf function executes even
though cells in Column O are not blank.

 

[T. Valko]

I get an Invalid Cell Reference warning when

I use your formulas below.

Hmmm....

Here's the formula modified to meet this condition:

cells in Column O are blank.

=SUMPRODUCT(('Old Mutual'!$L$7:$L$263="Issued")+
('Old Mutual'!$L$7:$L$263="Delivered"),
--('Old Mutual'!$O$7:$O$263=""))

And here's a screencap showing that the formula returns the correct result:

http://img167.imageshack.us/img167/8717/sumprt9.jpg