Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Murph
 
Posts: n/a
Default 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

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

had no
luck. Any help would be appreciated.

--
Brendan


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
Find Count of Items with certain criteria Tucson Guy Excel Discussion (Misc queries) 3 January 1st 05 03:11 PM
How do I set a Conditional Format for ROWs based on the value of . Wes T Excel Discussion (Misc queries) 3 December 29th 04 07:10 PM
Multiple rows of data on a single axis (charting) ramseysgirl Charts and Charting in Excel 8 December 29th 04 07:00 PM
Counting rows based on criteria in multiple cells Margaret Excel Discussion (Misc queries) 11 December 3rd 04 12:04 AM


All times are GMT +1. The time now is 12:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"