Countif multiple criteria

[Sir Babydum GBE]

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]

BD,
Are you talking formula or code?

[Sir Babydum GBE]

BD,
Are you talking formula or code?

Formula please. I like formulae

[rory]

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

[Bob Phillips]

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

The second INT is a tad redundant.

[rory]

But what's a tad between friends?

[Sir Babydum GBE]

Thanks! And where do I put the product match into the formula?

[Bob Phillips]

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

[Bob Phillips]

But what's a tad between friends?

A tad too much.

[rory]

=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]

Thanks Gentlemen.

[White_Nova]

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]

=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]

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]

A tad too much.

Is this 'Tid for Tad' ?

[figment]

what dose the "--" do in this function? i have never seen functions using this

[YellowLabPro]

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/arch...rray-formulas/

[figment]

thanks, thats good to know

[Bob Phillips]

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.