Multiple Count Condition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 05 Mar 2008 21:07 GMT

Hello all,

I'm trying to create a multiple condition count and I can't get it to work
using all the examples I've found. I'm hope someone can help me out.

Here's the formula I've trying to manipulate (unsuccessfully):
=COUNT(('Grindex 2008'!AB:AB="Jan-2")*('Grindex
2008'!AB:AB="Jan-3")*('Grindex 2008'!Y:Y="Jan-D"))

So if (column AB = "Jan-2" OR column AB = "Jan-3") AND column Y = "Jan-D"
then count the row.

I'm drawing a complete blank. Anyone know the correct format? I can't see
to find a good example on how to structure the formula.

Thanks,
Jay

Signature

Disregard, this is so I can find my post later.
***postedbyJay***

Reply to this Message

Jay - 05 Mar 2008 21:26 GMT

I also tried this but it doesn't produce the expected value.

=COUNT(IF(('Grindex 2008'!AB:AB="Jan-2")*('Grindex
2008'!AB:AB="Jan-3"),IF('Grindex 2008'!Y:Y="Jan-D",'Grindex 2008'!Y:Y)))

Signature

Disregard, this is so I can find my post later.
***postedbyJay***

> Hello all,
>
[quoted text clipped - 13 lines]
> Thanks,
> Jay

Reply to this Message

PCLIVE - 05 Mar 2008 21:31 GMT

One possible way:

=SUMPRODUCT(--('Grindex 2008'!AB1:AB100="Jan-2"),--('Grindex
2008'!Y1:Y100="Jan-D"))+SUMPRODUCT(--('Grindex
2008'!AB1:AB100="Jan-3"),--('Grindex 2008'!Y1:Y100="Jan-D"))

HTH,
Paul

> Hello all,
>
[quoted text clipped - 14 lines]
> Thanks,
> Jay

Reply to this Message

Jay - 06 Mar 2008 14:05 GMT

Paul,

The example you gave works well, but I have a question. Why do I need to
specify the rows in the formula?

I tried this, And I get a #NUM error.
=SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-2"),--('Grindex
2008'!Y:Y="Jan-D"))+SUMPRODUCT(--('Grindex 2008'!AB:AB="Jan-3"),--('Grindex
2008'!Y:Y="Jan-D"))

The only way I can try to get all rows is to use this:
=SUMPRODUCT(--('Grindex 2008'!AB1:AB65535="Jan-2"),--('Grindex
2008'!Y1:Y65535="Jan-D"))+SUMPRODUCT(--('Grindex
2008'!AB1:AB65535="Jan-3"),--('Grindex 2008'!Y1:Y65535="Jan-D"))

Any ideas?

Thanks,
Jay

Signature

Disregard, this is so I can find my post later.
***postedbyJay***

> One possible way:
>
[quoted text clipped - 23 lines]
> > Thanks,
> > Jay

Reply to this Message

PCLIVE - 06 Mar 2008 15:05 GMT

You cannot use full column ranges when using SUMPRODUCT. You must specify a
range.

> Paul,
>
[quoted text clipped - 46 lines]
>> > Thanks,
>> > Jay

Reply to this Message

Jay - 06 Mar 2008 17:20 GMT

Ah, didn't realize that.

Thanks!

Signature

Disregard, this is so I can find my post later.
***postedbyJay***

> You cannot use full column ranges when using SUMPRODUCT. You must specify a
> range.
[quoted text clipped - 49 lines]
> >> > Thanks,
> >> > Jay

Reply to this Message