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

 

[SkipVought]

Hi,

"if the statement below is true."

WHAT statement?

All you have is a for..next loop and an assignment.


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]

I meant if cell in Col A matches Cell in Col K then count corresponding cell in col BW that contains 1's. Thanks

 

[SkipVought]

Do you mean if the count is not zero then do something?

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]

Maybe this will help. I have to match everything by sales reps. On my "sales reps" sheet i have a list of current sales reps, and my "data sheet" is a list of items reviewed by sales rep number. My code i have posted is simply searching my data sheet and counting how many times each sales rep # appears. Everything Im about to lookup depends on cells in col A matching cells in col K as the cells on different sheets contain my sales reps #'s. So for rep 12 it would search col k and for each 12 i would need to know how many 1's there where in the corresponding cell in BW. If this is confusing I will try to do better next post.

 

[SkipVought]

I am a big VBA advocate, but i would not be doing this with VBA. I'd use native worksheet functions on the data sheet.
[tt]
=sumproduct(('Data Sheet'!$K$1:$K$65000=A2)*('Data Sheet'!$BW$1:$BW$65000))
[/tt]
where A2 contains a rep nbr on your rep sheet

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]

thats what people keep telling me, but the file when i use the function is too large. I need something smaller and by doing the vba I eliminate all of the issues concerning size. If I can just get past this step everything else should be very easy. Thanks.

 

[cbanks]

here is the correct statement in a function form..

=SUMPRODUCT(('Data Sheet'!$K$2:$K$500=A2)*('Data Sheet'!$BW$2:$BW$500=1))

 

[SkipVought]

How many rep#'s do you have on the rep sheet?

how many rows on data sheet?

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]

around 600 reps, and around 50000 rows on the data sheet

i have have around 15 cols on the reps sheet that all with be very similar to the problem i have asked for help on. Basical if the reps match i have a ton of 15 questions to answer. Which gives me my 15 cols.

 

[SkipVought]

Too big?

500 rows?

Geez! I use on 15,000 row sheets without blinking an eye.

MULTIPLE sumproducts, not just one.

I don't get the problem.

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]

Its just a large file that takes a terribly long time to calculate.

is there no way to do this in vba?

 

[SkipVought]

Yes but not the way that you are doing it.

in pseudo code...

for each rep in repsheet
   filter datasheet for rep
   SUM visible cells in column BW
next

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]

Just lost me

 

[cbanks]

OK. Im using the formual, but I need help. Col CC contains "C - Long Explanation" but when I make my formula look like this it does not count. Can it just count those that begin with C or contain the entire "C - Long Explanation"? Thanks

=SUMPRODUCT(('Data Sheet'!$K$2:$K$55000=A2)*('Data Sheet'!$CC$2:$CC$55000="C - long explanation"))

 

[SkipVought]

I assume that

For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)

is For Each rep in repsheet

Next macro record filtering the data sheet on a rep.

then add a statement to sum

n = Application.Sum(Range("BW:BW").SpecialCells(xlCellTypeVisible))

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]

Im clueless. I dont understand

 

[SkipVought]

Does

For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)

not loop thru each rep# in your rep sheet?

Or am I mistaken?

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 it loops through.

 

[SkipVought]

so it gets the first rep# and then autofilters the data on that rep#.

Can you do that?

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!}