Counting Multiple columns using contain

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"))
 
It's still returning 0 :-(

Dave Peterson said:
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"))
Click to expand...
 
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
 
Back
Top