WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
 

I have 2 columns: names in column F and dates in column G.

COL A COL B
JET 4/1
JET 4/1
JET 4/1
FRED 4/1

I am trying to calculate the number of times a person completed a project on
April 1, so the function needs both their initials and a date filled in, in
order to be counted as one.

I've tried several countif, sumif and sum product funtions and unable to get
any to work.
ie.
=SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1"))
=COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr")
=SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0)))

JET actually completed 3 projects on 4/1, but I always get a zero total. I
tried changing the date format, but that didn't work either.
Any help is appreciated.

Cheers
dblevins

WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
 

Try it this way:

=sumproduct(--(f2:f5="JET"),--(g2:g5=date(2009,4,1)))

Regards,
Fred

"dblevins" wrote in message
...
I have 2 columns: names in column F and dates in column G.

COL A COL B
JET 4/1
JET 4/1
JET 4/1
FRED 4/1

I am trying to calculate the number of times a person completed a project
on
April 1, so the function needs both their initials and a date filled in,
in
order to be counted as one.

I've tried several countif, sumif and sum product funtions and unable to
get
any to work.
ie.
=SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1"))
=COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr")
=SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0)))

JET actually completed 3 projects on 4/1, but I always get a zero total. I
tried changing the date format, but that didn't work either.
Any help is appreciated.

Cheers
dblevins

WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
 

You were quite close:

=SUMPRODUCT((F2:F1308="JET")*(MONTH(G2:G1308)=4))

--
Gary''s Student - gsnu200845

WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
 

Excellent! This worked. Thank you so much!
--
DB


"Fred Smith" wrote:

Try it this way:

=sumproduct(--(f2:f5="JET"),--(g2:g5=date(2009,4,1)))

Regards,
Fred

"dblevins" wrote in message
...
I have 2 columns: names in column F and dates in column G.

COL A COL B
JET 4/1
JET 4/1
JET 4/1
FRED 4/1

I am trying to calculate the number of times a person completed a project
on
April 1, so the function needs both their initials and a date filled in,
in
order to be counted as one.

I've tried several countif, sumif and sum product funtions and unable to
get
any to work.
ie.
=SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1"))
=COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr")
=SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0)))

JET actually completed 3 projects on 4/1, but I always get a zero total. I
tried changing the date format, but that didn't work either.
Any help is appreciated.

Cheers
dblevins

WHETHER TO USE COUNTIF, SUMIF or SUMPRODUCT
 

Glad I could help. Thanks for the feedback.

Regards,
Fred.

"dblevins" wrote in message
...
Excellent! This worked. Thank you so much!
--
DB


"Fred Smith" wrote:

Try it this way:

=sumproduct(--(f2:f5="JET"),--(g2:g5=date(2009,4,1)))

Regards,
Fred

"dblevins" wrote in message
...
I have 2 columns: names in column F and dates in column G.

COL A COL B
JET 4/1
JET 4/1
JET 4/1
FRED 4/1

I am trying to calculate the number of times a person completed a
project
on
April 1, so the function needs both their initials and a date filled
in,
in
order to be counted as one.

I've tried several countif, sumif and sum product funtions and unable
to
get
any to work.
ie.
=SUMPRODUCT((F2:F1308="JET")*(G2:G1308="4/1"))
=COUNTIF(F2:F1308,"=JET")-COUNTIF(G2:G1308,"=1-Apr")
=SUM(IF(F2:F1308="JET",IF(G2:G1308="April 1, 2009",1,0)))

JET actually completed 3 projects on 4/1, but I always get a zero
total. I
tried changing the date format, but that didn't work either.
Any help is appreciated.

Cheers
dblevins