SUMIF or COUNTIF

[deletion files]

I am trying to figure out the formula which would count the number of "s" (or
"c" or "d" for that matter) files that are also have a received date equal to
A1 (9-03).

I have tried several formulas - no luck.

Any ideas?


9-03
Open New
C. Camp 1 COUNT((E8:E30)="A1",IF(C8:C30),"S"))
Deletions 18 IF(E8:E30="A1",COUNT(C8:C30="S")"1")
Disputes 1 IF(E8:E30="A1",COUNT(C8:C30="S")" ")

Starts File Type Recv'd
09-12-2009 D 9-03
10-02-2009 D 8-31
10-02-2009 D 8-27
10-14-2009 D 7-28
10-17-2009 D 9-02
11-07-2009 D 7-21
11-27-2009 D 8-17
11-29-2009 D 8-31
12-19-2009 D 8-04
12-19-2009 D 8-04
12-19-2009 D 8-04
01-15-2010 D 7-27
02-21-2010 D 4-21
03-27-2010 D 8-31
04-09-2010 D 8-06
04-25-2010 D 8-24
07-03-2010 D 8-20
05-29-2011 D 8-24
06-01-2010 S 9-03
07-01-2010 C 9-03

 

[Dave Peterson]

If you're using xl2007, look at =sumifs() or =countifs() in excel's help.

If you're using any version, you could use a formula like:

=sumproduct(--(e8:e30=a1),--(c8:c30="S"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

======
If those values in E8:E30 and A1 are dates (real dates with month, day and
year!) and you wanted to know at the month level (9-03 means all of September of
2003???), you could use a formula like:

=sumproduct(--(text(e8:e30,"yyyymm")=text(a1,"yyyymm")),--(c8:c30="S"))