PDA

View Full Version : Countif multiple criteria



Sir Babydum GBE
09-21-2007, 07:38 AM
Say column A contains a list of Dates and Times (so the serial number will have something after the decimal)
Column B a list of products

How do I do a countif that works out how many of a given product were sold on a given day?

Thanks

YellowLabPro
09-21-2007, 07:47 AM
BD,
Are you talking formula or code?

Sir Babydum GBE
09-21-2007, 07:52 AM
BD,
Are you talking formula or code?

Formula please. I like formulae

rory
09-21-2007, 07:54 AM
Formula:
=SUMPRODUCT(--(INT($A$1:$A$100)=INT(DATE(2007,9,21))))
for example.

Bob Phillips
09-21-2007, 08:05 AM
Formula:
=SUMPRODUCT(--(INT($A$1:$A$100)=INT(DATE(2007,9,21))))
for example.

The second INT is a tad redundant.

rory
09-21-2007, 08:15 AM
But what's a tad between friends? :)

Sir Babydum GBE
09-21-2007, 08:17 AM
Thanks! And where do I put the product match into the formula?

Bob Phillips
09-21-2007, 08:21 AM
=SUMPRODUCT(--(INT($A$1:$A$100)=DATE(2007,9,21)),--(B1:B100="product"))

Bob Phillips
09-21-2007, 08:21 AM
But what's a tad between friends? :)

A tad too much.

rory
09-21-2007, 08:22 AM
=SUMPRODUCT((INT($A$1:$A$100)=DATE(2007,9,21))*($B$1:$B$100="Product name"))
You can replace the date and product name with cell references containing the values you want.

Sir Babydum GBE
09-21-2007, 09:05 AM
Thanks Gentlemen.

White_Nova
11-09-2007, 12:24 AM
Same question just in another way?
If i have a list of data and a template with formulas in to pull info through...

I would like to have a countif and sumif axample if i can search between two seperate date???

Bob Phillips
11-09-2007, 05:11 AM
=SUMPRODUCT(--(rng_date>=--"2007-01-01"),--(rng_date)<="2007-02-01"))

to count them

=SUMPRODUCT(--(rng_date>=--"2007-01-01"),--(rng_date)<="2007-02-01"),rng_amount)

to sum them

rory
11-09-2007, 05:11 AM
If your start and end date criteria are in C1 and d1, then:
Count: =SUMPRODUCT(($A$1:$A$100>=$C$1)*($A$1:$A$100<=$D$1))
Sum (of F): =SUMPRODUCT(($A$1:$A$100>=$C$1)*($A$1:$A$100<=$D$1)*$F$1:$F$100)

unmarkedhelicopter
11-09-2007, 05:21 AM
A tad too much.Is this 'Tid for Tad' ?

figment
11-09-2007, 06:13 AM
what dose the "--" do in this function? i have never seen functions using this

YellowLabPro
11-09-2007, 06:18 AM
The double negatives:
To force Excel to coerce the booleans appropriately, two negative signs are added to the formula. Excel negates the result, then negates it again which is enough to make it work.

http://www.dailydoseofexcel.com/archives/2004/05/06/counting-with-array-formulas/

figment
11-09-2007, 06:24 AM
thanks, thats good to know

Bob Phillips
11-09-2007, 06:29 AM
what dose the "--" do in this function? i have never seen functions using this

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.