Can I combine CountIf with IF or AND?
 

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:M 264,"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:O 264 0),N8:N264))
This also doesn't work. Any tips?

Can I combine CountIf with IF or AND?
 

Are you wanting a count or a sum?

Here's the sum:

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

Here's the count:

=SUMPRODUCT((M8:M264="Issued")+(M8:M264="Delivered "),--(O8:O2640))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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?

Can I combine CountIf with IF or AND?
 

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" wrote:

Are you wanting a count or a sum?

Here's the sum:

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

Here's the count:

=SUMPRODUCT((M8:M264="Issued")+(M8:M264="Delivered "),--(O8:O2640))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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?

Can I combine CountIf with IF or AND?
 

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))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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" wrote:

Are you wanting a count or a sum?

Here's the sum:

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

Here's the count:

=SUMPRODUCT((M8:M264="Issued")+(M8:M264="Delivered "),--(O8:O2640))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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?

Can I combine CountIf with IF or AND?
 

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" wrote:

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))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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" wrote:

Are you wanting a count or a sum?

Here's the sum:

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

Here's the count:

=SUMPRODUCT((M8:M264="Issued")+(M8:M264="Delivered "),--(O8:O2640))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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?

Can I combine CountIf with IF or AND?
 

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

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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" wrote:

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))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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" wrote:

Are you wanting a count or a sum?

Here's the sum:

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

Here's the count:

=SUMPRODUCT((M8:M264="Issued")+(M8:M264="Delivered "),--(O8:O2640))

--
Biff
Microsoft Excel MVP


"Gmolatore" wrote in message
...
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?