Counting Multiple Criteria

[behrk2]

Hello all,

Please help! I am trying to write a function that searches through all the rows and allows me to count data (about 700 rows worth) so long as it meets certain specifications. The specifications are, for this example, as follows:

Column B = "Archives"
Column C = "Assigned"
Column D = "Distributed Systems"

If those specs are met, I want to increment the count by 1. This data appears on Sheet1, I want to store the result in Sheet2...here is what I have so far...



Function Count_Dist_Open(Rng As Range, Rng1 As Range) As Integer

With Sheet1

Dim Cll As Range

For Each Cll In Rng

If Rng = "Assigned" And Rng1 = Archives Then
Count_Dist_Open = Count_Dist_Open + 1
End If

Next Cll

End With

End Function




But this doesn't work. I just don't know how to properly code this. Can anyone help? Thanks!

[geekgirlau]

You can use a formula for this:

=SUMPRODUCT(($B$2:$B$20="Archives")*($C$2:$C$20="Assigned")*($D$2:$D$20="Distributed Systems")*1)

[behrk2]

ah thank you so much! that works perfectly i have been trying to find out how to do that for awhile now. i appreciate your help.

[behrk2]

is there anyway i can edit this formula to add some OR's? for instance

=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned")*(Sheet1!$D$2:$D$633="Distributed Systems")*1)

Count if Sheet1!$D$2:$D$633 = "Distributed Systems" OR "RSC File Restore"?

[geekgirlau]

=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned") *(OR(Sheet1!$D$2:$D$633="Distributed Systems",$D$2:$D$633="RSC File Restore"))*1)

[Bob Phillips]

The *1 is totally superfluous

=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned") *(OR(Sheet1!$D$2:$D$633="Distributed Systems",$D$2:$D$633="RSC File Restore")))

[rory]

You can't actually use OR that way in a SUMPRODUCT formula - it needs to be:
=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned") *((Sheet1!$D$2:$D$633="Distributed Systems")+($D$2:$D$633="RSC File Restore")))

otherwise if any cell in the D column range equals either of those values, you get a count of all the rows where B = "Archives" and C = "Assigned", no matter what is in D for those rows.
Regards,
Rory

[rory]

PS If you only have one or criterion, you can also use this syntax:
=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned") *(Sheet1!$D$2:$D$633={"Distributed Systems","RSC File Restore"}))
but this will not work properly if you want multiple options for multiple columns.
FWIW.
Rory

[Bob Phillips]

You can't actually use OR that way in a SUMPRODUCT formula - it needs to be:
=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned") *((Sheet1!$D$2:$D$633="Distributed Systems")+($D$2:$D$633="RSC File Restore")))

That is an incorrect statement.



=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned")*
(OR(Sheet1!$D$2:$D$633="Distributed Systems",$D$2:$D$633="RSC File Restore")))

works perfectly well. What doesn't work is



=SUMPRODUCT(--(Sheet1!$B$2:$B$633="Archives"),--(Sheet1!$C$2:$C$633="Assigned"),
--(OR(Sheet1!$D$2:$D$633="Distributed Systems",$D$2:$D$633="RSC File Restore")))

whilst the double unary does work with +



=SUMPRODUCT(--(Sheet1!$B$2:$B$633="Archives"),--(Sheet1!$C$2:$C$633="Assigned"),
--((Sheet1!$D$2:$D$633="Distributed Systems")+($D$2:$D$633="RSC File Restore")))

Similarly whilst this works



=SUMPRODUCT((Sheet1!$B$2:$B$633="Archives")*(Sheet1!$C$2:$C$633="Assigned")*
(Sheet1!$D$2:$D$633={"Distributed Systems","RSC File Restore"}))

the double unary doesn't in this style



=SUMPRODUCT(--(Sheet1!$B$2:$B$633="Archives"),--(Sheet1!$C$2:$C$633="Assigned"),
--(Sheet1!$D$2:$D$633={"Distributed Systems","RSC File Restore"}))

[rory]

So you're saying that the attached, using your first formula, is correct then?
OR evaluates the array within its parentheses and returns one result, not an array. I don't really see the relevance of the rest?

[Bob Phillips]

You are absolutely right, my apologies.

I started thinking that OR didn't work, although I have it in the back of my mind that it does in some circumstance (probably with COUNTIF, but it is irrelevant because there are other methods), so when I saw GGAU's post, I tried it. It worked in my tests, but I can see now that it was due to inadequate testing on my part. The fact that I can explain why it produces the results that it does doesn't disguise my shame :-(

[rory]

Not a problem - been there, done that! :) I was sort of hoping you were going to prove me wrong though...would have been handy.
Rory

[geekgirlau]

Ugh - yes my very extensive (okay 10 second) testing had the OR working, but on further investigation you are correct :omg2:


The *1 is totally superfluous

Yep, aware of this one. Whilst not required, I have found in the past that it's handy to identify situations where I'm using SUMPRODUCT to produce a count, versus producing the sum of a specific field (such as amount or quantity). So this falls into the category of a personal convention rather than a technical requirement.