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.

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



sesler2 wrote:
>
> 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.

--

Dave Peterson

It's still returning 0 :-(

"Dave Peterson" wrote:

> 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"))
>
>
>
> sesler2 wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

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

"Dave Peterson" wrote:

> 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"))
>
>
>
> sesler2 wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>