COUNTIF? DCOUNT?

[Steve]

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?

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A2:A1000>100),--(B2:B1000>6000))

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]

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.

 

[JE McGimpsey]

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.

 

[Peo Sjoblom]

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

 

[Ashish Mathur]

Hi,

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

SUM(IF((B3:B9="a")*(C3:C9>10),1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Shane Devenshire]

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

 

[Peo Sjoblom]

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:C9>10))

and save some characters (6)



--


Regards,


Peo Sjoblom

 

[Steve]

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(--(D265535>100),--(F2:F65535>6000))

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.

 

[Steve]

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).