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
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.
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.
=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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.