CountIf Multiple Criteria

[cbanks]

Sorry to start another post but I was new and I marked my last post complete before it actually was. Below is the code I am using. It works great, but I also need to add to it. In addition to what is below I need this to count the corresponding cells in col BW sheet "data sheet" that contain a 1 if the statement below is true. THanks

For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
        ce.Offset(0, 2) = WorksheetFunction.CountIf(Worksheets("Data Sheet").Range("k:k"), ce.Value)
    
        
Next ce

 

[cbanks]

Sorry I can not

 

[SkipVought]

You go to the data sheet, Data>Filter>AutoFilter and select the rep# in that data column.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[cbanks]

yes, but my sales reps can change every month/week. New ones are added and deleted constantly.

 

[SkipVought]

So what? Are we not getting a count as of NOW?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[cbanks]

this works. but it counts all blanks..

   With Range("D2:D5000")
    .FormulaR1C1 = "=SUMPRODUCT(('Data Sheet'!R2C11:R5000C1=RC1)*('Data Sheet'!R2C75:R5000C75=1))"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
   End With

 

[SkipVought]

R2C11:R5000C1

column 1 or column 11?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[cbanks]

Yes i corrected that but my sales reps end at 600 today and i have it set to 5000 keeps filling all the way to 5000. Also for the second search i get a return of NAME. Why is that? Can you tell me how to make it search for just a capital A and not the whole explanation after it.

 With Range("D2:D5000")
    .FormulaR1C1 = "=SUMPRODUCT(('Data Sheet'!R2C11:R5000C11=RC1)*('Data Sheet'!R2C75:R5000C75=1))"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
   End With
      With Range("E2:E5000")
    .FormulaR1C1 = "=SUMPRODUCT(('Data Sheet'!R2C11:R5000C11=RC1)*('Data Sheet'!R2C81:R5000C81=A - No initial comment to explain omission of incidents))"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
   End With

 

[SkipVought]

Don't reference more rows than the data you currently have.

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

..How can I rename a table as it changes size FAQ68-1331

Offset is volatile, Counta skips blanks (non-contiguous) and Indirect is inefficient.

Check out these links:

http://www.xldynamic.com/source/xld.LastValue.html

http://www.mrexcel.com/board2/viewtopic.php?p=501680#501680

If using Excel 2003, check out the List functionality as well.

-----------
Regards,
Zack Barresse

 

[SkipVought]

Ya know zack, I've been around the block a time or two with the OFFSET function. And using sound database principles, like blanks are not keys, COUNTA works just great.

If you'd like to post an alternative solution, feel free. Your allegations, however, are hollow. Of course, OFFSET is volatile. I want it to be. It has to be to work. In fact, ANY interactive function better be volatile.

I'll stand behind that approch any day. I stand behind the statements I make and if I'm wrong, I admit it and don't pretend I cannot understand exchanges of ideas that challenge contradictory statements.



Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

No Skip, you are completely missing the point here. What you posted does work indeed and I am not trying to make it sound any less of a working solution. I personally do not like that method so I wanted to post alternative methods. There are pro's and con's to each. I'm sure you've had a lot of luck with your approach, but for the majority of the data structure's out there, it's not as efficient as it could be.

-----------
Regards,
Zack Barresse