I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

One way

=SUMPRODUCT(--(A2:A1000100),--(B2:B10006000))

using DCOUNT assuming the headers are Demand and Amount
and that they are in A1 and B1, assume the criteria range is H1:I2
and looks like

Demand Amount
100 6000


then this formula will give you the count


=DCOUNT(A1:B1000,"Amount",H1:I2)




Adapt to fit your own table/ranges



--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

Thanks, but I still can't make it work... If I use the AutoFilter and custom
sorts, I can manually get the answers... but the formulae provided aren't
getting me to those answers.
--
Grateful


"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A1000100),--(B2:B10006000))

using DCOUNT assuming the headers are Demand and Amount
and that they are in A1 and B1, assume the criteria range is H1:I2
and looks like

Demand Amount
100 6000


then this formula will give you the count


=DCOUNT(A1:B1000,"Amount",H1:I2)




Adapt to fit your own table/ranges



--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

Saying that "the formulae provided aren't getting me to those answers"
is not particularly helpful. Are you getting an error? an erroneous
value? a crash?

Did you adjust the ranges for your particular worksheet? There's no
reason that, if your values are numeric, and if you used the proper
ranges, that the formulae won't get you correct answers.



In article ,
Steve wrote:

Thanks, but I still can't make it work... If I use the AutoFilter and custom
sorts, I can manually get the answers... but the formulae provided aren't
getting me to those answers.
--
Grateful


"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A1000100),--(B2:B10006000))

using DCOUNT assuming the headers are Demand and Amount
and that they are in A1 and B1, assume the criteria range is H1:I2
and looks like

Demand Amount
100 6000


then this formula will give you the count


=DCOUNT(A1:B1000,"Amount",H1:I2)




Adapt to fit your own table/ranges



--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

The formula provided will get the results after the cell references have
been changed to the correct ones provided that the numbers are numbers and
not text numbers or a mix. This is pretty straightforward and that formula
will count where A2:A1000 is greater than 100 AND where B2:B100 is greater
than 6000


--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
Thanks, but I still can't make it work... If I use the AutoFilter and
custom
sorts, I can manually get the answers... but the formulae provided aren't
getting me to those answers.
--
Grateful


"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A1000100),--(B2:B10006000))

using DCOUNT assuming the headers are Demand and Amount
and that they are in A1 and B1, assume the criteria range is H1:I2
and looks like

Demand Amount
100 6000


then this formula will give you the count


=DCOUNT(A1:B1000,"Amount",H1:I2)




Adapt to fit your own table/ranges



--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
I have a very large worksheet... One column contains the NUMBER of
demands
for an item in a given year; another column contains the DOLLAR VALUE
of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand
is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any
help?
--
Grateful

Hi,

You can also try the following array formula (Ctrl+Shift=Enter)

SUM(IF((B3:B9="a")*(C3:C910),1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

Hi,

Well there could be a problem in the "annual demand" - does the Dollar Value
column contain the annual demand or does it contain Demand?

Thanks,
Shane Devenshire

"Steve" wrote:

I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

No need to use IF and 1 if you want an array version which is really
obsolete given that sumproduct is faster.

However you might as well use

=SUM((B3:B9="a")*(C3:C910))

and save some characters (6)



--


Regards,


Peo Sjoblom

"Ashish Mathur" wrote in message
...
Hi,

You can also try the following array formula (Ctrl+Shift=Enter)

SUM(IF((B3:B9="a")*(C3:C910),1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
I have a very large worksheet... One column contains the NUMBER of
demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

Thanks all... Each row represents a part number. Column D has the number of
requisitions for that part; Column F has the Annual Demand Value of those
requisitions. I need to find the items that have requisitions 150, AND
annual demand value $6,000.

When I use:
=SUMPRODUCT(--(D2:D65535100),--(F2:F655356000))

I get a value of 62.

If I use an Advanced Filter for those two columns, I get 26. This is what I
meant when I said it was giving me the answer I was looking for. I can use
this advanced filter on each worksheet...

Thanks.

--
Grateful


"Shane Devenshire" wrote:

Hi,

Well there could be a problem in the "annual demand" - does the Dollar Value
column contain the annual demand or does it contain Demand?

Thanks,
Shane Devenshire

"Steve" wrote:

I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful

PS - the trouble with this method is that it sorts the data, but doesn't give
me the COUNT of parts... which gets me back to why I was looking for a f(x).
--
Grateful


"Steve" wrote:

Thanks all... Each row represents a part number. Column D has the number of
requisitions for that part; Column F has the Annual Demand Value of those
requisitions. I need to find the items that have requisitions 150, AND
annual demand value $6,000.

When I use:
=SUMPRODUCT(--(D2:D65535100),--(F2:F655356000))

I get a value of 62.

If I use an Advanced Filter for those two columns, I get 26. This is what I
meant when I said it was giving me the answer I was looking for. I can use
this advanced filter on each worksheet...

Thanks.

--
Grateful


"Shane Devenshire" wrote:

Hi,

Well there could be a problem in the "annual demand" - does the Dollar Value
column contain the annual demand or does it contain Demand?

Thanks,
Shane Devenshire

"Steve" wrote:

I have a very large worksheet... One column contains the NUMBER of demands
for an item in a given year; another column contains the DOLLAR VALUE of
those demands. I need to COUNT the number of rows where the NUMBER of
demands is greater than 100 and the DOLLAR VALUE of the annual demand is
greater than $6000. Been struggling with COUNTIF and DCOUNT. Any help?
--
Grateful