COunt if Question

  • Thread starter Thread starter James
  • Start date Start date
J

James

What is the proper syntax to concatnate Countif function

Say you want Colum A = to equal either 100 or 200 and colum B should
be a date between Jan 1, 2008 and Dec 1 2008
 
List the criteria in some cells:

D1 = 100
D2 = 200
E1 = 1/1/2008
F1 = 12/1/2008

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A20,D1:D2,0))),--(B1:B20>=E1),--(B1:B20<=F1))
 
Hi,

In 2007 you can write

=SUMPRODUCT(COUNTIFS(A1:A21,G1:G2,B1:B21,">="&G3,B1:B21,"<="&G4))

Assuming G1:G4 are 100, 200, 1/1/2008, and 12/1/2008 respectively. You can
shorten the formula by changing the dates to 12/31/2007 and 12/2/2008 and
then using

=SUMPRODUCT(COUNTIFS(A1:A21,G1:G2,B1:B21,">"&G3,B1:B21,"<"&G4))

In 2003 or 2007 you can use

=SUMPRODUCT((A7:A21={100,200})*(B7:B21>=G3)*(B7:B21<=G4))
or
=SUMPRODUCT((A7:A21=G1:H1)*(B7:B21>=G3)*(B7:B21<=G4))

if you put 100 and 200 in G1:H1 respectively.
 
Back
Top