Count rows based on multiple criteria

[Guest]

I'm having trouble figuring out a way to count specific rows in a database I
have, using multiple variables. For example. I have column A, B, and C. In
column "A" are number values from 1-10. In Column "B" are peoples names, and
in Column "C" are other text values. I want to figure out a way to count all
the rows that have the numbers 1-3 in column "A", the same name in column
"B", and the same text value in column "C". For example all the rows that
have a value between 1 and 7 in column A, value of "Jones" in Column B, and a
value of "Open" in column C. In additions to that I also need to come up
with a formula that does what I described above, but in addition, subtracts
and rows that have a specific value in Column "D". I've tried using multiple
variations of the COUNT, DCOUNT, SUM, and DSUM functions but have had no
luck. Any help would be appreciated.

 

[Pantryman]

Hi Ralph,

try using SUMPRODUCT

I hope that this is of any help, as I'm not sure that this covers you
problem.

It should though

 

[Guest]

Hi
try
=SUMPRODUCT(--(A1:A10>=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="Open"))

an
=SUMPRODUCT(--(A1:A10>=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="Open"),D110)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Spalding]

Wow, thank you guys! SUMPRODUCT used that way is also just what I'v
been looking for.

I was trying to use DCOUNTA, which works well if the case is simpl
enough. But this technique allows me to use <> with text, which is
huge help, since I often want to screen out 2 text items out of abou
8, and DCOUNTA as far as I know won't take a criteria for not equal to
Therefore I had to explicitly list all 6 I did want, and then once fo
each matching value in another field, which got very cumbersome.
SUMPRODUCT solves it all. I'm still working on figuring out tha
matrix notation, but as long as it works, I can live with a littl
mystery

 

[Guest]

Frank,

I've tried your suggestion and it seems to be working. I'm just coming
across a couple of snags. All my data in on sheet 1. I want my calculations
to occur on sheet 2. I can get your formula to work on sheet 1, where the
data is stored, but when I try in on sheet 2, where it references the data on
sheet 1, I keep getting an error. This is the formula I am using. Do you
see a problem with it?

=SUMPRODUCT(--(('749 Open-Pending Inventory'!A2:A99999)>=1),--(('749
Open-Pending Inventory'!A2:A99999)<=13))

If there is an issue, how do I correct it?

Second question I have is regarding how to excluded certain values from my
count. For example, using the above calculation, I also want to exclude any
rows that have a value in Column "B" of, "small", or "medium". I didn't
quite understand your second formula. How would this work?

Brendan

 

[Frank Kabel]

Hi
yes
Excel does not have 99999 rows . Try:
=SUMPRODUCT(--('749 Open-Pending Inventory'!A2:A9999>=1),--('749
Open-Pending Inventory'!A2:A9999<=13))