ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Count rows based on multiple criteria (https://www.excelbanter.com/excel-worksheet-functions/5177-re-count-rows-based-multiple-criteria.html)

 Murph October 28th 04 03:19 AM

Count rows based on multiple criteria

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" wrote:

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

and
=SUMPRODUCT(--(A1:A10=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="Open"),D1:D10)

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

"Murph" wrote:

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.

--
Brendan

 Frank Kabel October 28th 04 07:13 AM

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Murph" schrieb im Newsbeitrag
...
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" wrote:

Hi
try

=SUMPRODUCT(--(A1:A10=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="O
pen"))

and

=SUMPRODUCT(--(A1:A10=1),--(A1:A10<=7),--(B1:B10="Johns"),--(C1:C10="O
pen"),D1:D10)

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

"Murph" wrote:

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