Counting Multiple columns using contain

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sesler2 - 22 Aug 2007 00:54 GMT

I am trying to produce calculations for a sheet , broken down by month where
I want to use contain somehow with sumproduct or countif but having no joy.
Any help would be appreciated. The 2 formulas I have tried are:

=SUMPRODUCT(--($A$2:$A$573="Aug*"),--($D$2:$D$573="App*"),--($F$2:$F$573="AUTO*"))

and

=COUNT(IF($A$2:$A$573="Aug*",IF($D$2:$D$573="App*",IF($F$2:$F$573="AUTO*",$C$2:$C$573))))

I will have for each month 4 scenarios:
contains App and contains AUTO
contains App and doesn't contain AUTO
doesn't contain App and contains AUTO
doesn't contain App and doesn't contain AUTO

So once I get one formula correct, the other 3 will be not a problem.

Reply to this Message

Dave Peterson - 22 Aug 2007 01:11 GMT

I'd try:

=SUMPRODUCT(--(left($A$2:$A$573,3)="Aug"),
--(left($D$2:$D$573,3)="App"),
--(left($F$2:$F$573,4)="AUTO"))

> I am trying to produce calculations for a sheet , broken down by month where
> I want to use contain somehow with sumproduct or countif but having no joy.
[quoted text clipped - 13 lines]
>
> So once I get one formula correct, the other 3 will be not a problem.

Signature

Dave Peterson

Reply to this Message

sesler2 - 22 Aug 2007 01:28 GMT

It's still returning 0 :-(

> I'd try:
>
[quoted text clipped - 19 lines]
> >
> > So once I get one formula correct, the other 3 will be not a problem.

Reply to this Message

sesler2 - 22 Aug 2007 02:12 GMT

Dave, please ignore first reply. Silly me - the cell value is 01/08/2006 so
when I paste the Month values in cells e.g h2 = 01/08/2006 and refer to h2
in the first condition below it works fine. Thanks for your help,
appreciated :-)
Shaz

> I'd try:
>
[quoted text clipped - 19 lines]
> >
> > So once I get one formula correct, the other 3 will be not a problem.

Reply to this Message