Sum using two criteria
 

Hello,

Is it possible to sum up all contracts won in a given
month? This is the information I enter into excel.
Kit#, Submit Date, Award Date, # of Kits, Price Quoted,
Won / Lost. The date format is 4-Mar-97. I only want to
sum the awards that have Won in the Won / Lost Colomn.
Here is the formula I used.{=SUM((C13:C165="Mar")*
(L13:L196="Won")*(J13:J165))}. Thanks in advance for
your help.

Mark

If you really have the date in column C:

=SUM((MONTH(C13:C165)=3)*(L13:L165="Won")*(J13:J16 5))
(array entered, still)

And those ranges have to be the same size (I changed L13:L196 to L13:L165).

This formula is equivalent, but it's not array entered:

=SUMPRODUCT(--(MONTH(C13:C165)=3),--(L13:L165="Won"),(J13:J165))

The double minuses convert trues/falses to +1/0's.

And =sumproduct() likes to work with numbers--not logical values.

Mark wrote:

Hello,

Is it possible to sum up all contracts won in a given
month? This is the information I enter into excel.
Kit#, Submit Date, Award Date, # of Kits, Price Quoted,
Won / Lost. The date format is 4-Mar-97. I only want to
sum the awards that have Won in the Won / Lost Colomn.
Here is the formula I used.{=SUM((C13:C165="Mar")*
(L13:L196="Won")*(J13:J165))}. Thanks in advance for
your help.

Mark

--

Dave Peterson

I used the array you suggested and I get this error:
#VALUE! Any ideas as to why I get this? I appreciate
your help


-----Original Message-----
If you really have the date in column C:

=SUM((MONTH(C13:C165)=3)*(L13:L165="Won")*(J13:J1 65))
(array entered, still)

And those ranges have to be the same size (I changed
L13:L196 to L13:L165).

This formula is equivalent, but it's not array entered:

=SUMPRODUCT(--(MONTH(C13:C165)=3),--(L13:L165="Won"),
(J13:J165))

The double minuses convert trues/falses to +1/0's.

And =sumproduct() likes to work with numbers--not
logical values.

Mark wrote:

Hello,

Is it possible to sum up all contracts won in a given
month? This is the information I enter into excel.
Kit#, Submit Date, Award Date, # of Kits, Price Quoted,
Won / Lost. The date format is 4-Mar-97. I only want
to
sum the awards that have Won in the Won / Lost Colomn.
Here is the formula I used.{=SUM((C13:C165="Mar")*
(L13:L196="Won")*(J13:J165))}. Thanks in advance for
your help.

Mark

--

Dave Peterson
.

Did you array enter it (ctrl-shift-enter)?

Did you have any errors in any of those ranges?

wrote:

I used the array you suggested and I get this error:
#VALUE! Any ideas as to why I get this? I appreciate
your help

-----Original Message-----
If you really have the date in column C:

=SUM((MONTH(C13:C165)=3)*(L13:L165="Won")*(J13:J1 65))
(array entered, still)

And those ranges have to be the same size (I changed
L13:L196 to L13:L165).

This formula is equivalent, but it's not array entered:

=SUMPRODUCT(--(MONTH(C13:C165)=3),--(L13:L165="Won"),
(J13:J165))

The double minuses convert trues/falses to +1/0's.

And =sumproduct() likes to work with numbers--not
logical values.

Mark wrote:

Hello,

Is it possible to sum up all contracts won in a given
month? This is the information I enter into excel.
Kit#, Submit Date, Award Date, # of Kits, Price Quoted,
Won / Lost. The date format is 4-Mar-97. I only want
to
sum the awards that have Won in the Won / Lost Colomn.
Here is the formula I used.{=SUM((C13:C165="Mar")*
(L13:L196="Won")*(J13:J165))}. Thanks in advance for
your help.

Mark

--

Dave Peterson
.


--

Dave Peterson

I did use (ctrl-shift-enter) I didn't have any errors.


-----Original Message-----
Did you array enter it (ctrl-shift-enter)?

Did you have any errors in any of those ranges?

wrote:

I used the array you suggested and I get this error:
#VALUE! Any ideas as to why I get this? I appreciate
your help

-----Original Message-----
If you really have the date in column C:

=SUM((MONTH(C13:C165)=3)*(L13:L165="Won")*(J13:J1 65))
(array entered, still)

And those ranges have to be the same size (I changed
L13:L196 to L13:L165).

This formula is equivalent, but it's not array
entered:

=SUMPRODUCT(--(MONTH(C13:C165)=3),--(L13:L165="Won"),
(J13:J165))

The double minuses convert trues/falses to +1/0's.

And =sumproduct() likes to work with numbers--not
logical values.

Mark wrote:

Hello,

Is it possible to sum up all contracts won in a
given
month? This is the information I enter into excel.
Kit#, Submit Date, Award Date, # of Kits, Price
Quoted,
Won / Lost. The date format is 4-Mar-97. I only
want
to
sum the awards that have Won in the Won / Lost
Colomn.
Here is the formula I used.{=SUM((C13:C165="Mar")*
(L13:L196="Won")*(J13:J165))}. Thanks in advance
for
your help.

Mark

--

Dave Peterson
.


--

Dave Peterson
.

Hi!

Are your dates true Excel dates or are they text strings?

Biff

-----Original Message-----
I did use (ctrl-shift-enter) I didn't have any errors.


-----Original Message-----
Did you array enter it (ctrl-shift-enter)?

Did you have any errors in any of those ranges?

wrote:

I used the array you suggested and I get this error:
#VALUE! Any ideas as to why I get this? I appreciate
your help

-----Original Message-----
If you really have the date in column C:

=SUM((MONTH(C13:C165)=3)*(L13:L165="Won")*(J13:J1 65))
(array entered, still)

And those ranges have to be the same size (I changed
L13:L196 to L13:L165).

This formula is equivalent, but it's not array
entered:

=SUMPRODUCT(--(MONTH(C13:C165)=3),--(L13:L165="Won"),
(J13:J165))

The double minuses convert trues/falses to +1/0's.

And =sumproduct() likes to work with numbers--not
logical values.

Mark wrote:

Hello,

Is it possible to sum up all contracts won in a
given
month? This is the information I enter into excel.
Kit#, Submit Date, Award Date, # of Kits, Price
Quoted,
Won / Lost. The date format is 4-Mar-97. I only
want
to
sum the awards that have Won in the Won / Lost
Colomn.
Here is the formula I used.{=SUM((C13:C165="Mar")*
(L13:L196="Won")*(J13:J165))}. Thanks in advance
for
your help.

Mark

--

Dave Peterson
.


--

Dave Peterson
.

.

And to piggyback on Biff's question.

If they really are text strings '4-Mar-97, try selecting that column and doing:

Data|Text to columns
Fixed width (don't add any lines and remove any that excel guessed at)
choose dmy as the field format
and plop it right back where you got it.

This should convert those text strings to real dates.

If there is a reason you can't change the strings to actual dates:

=SUMPRODUCT(--(ISNUMBER(SEARCH("mar",C13:C165))),--(L13:L165="Won"),(J13:J165))

Biff wrote:

Hi!

Are your dates true Excel dates or are they text strings?

Biff

-----Original Message-----
I did use (ctrl-shift-enter) I didn't have any errors.


-----Original Message-----
Did you array enter it (ctrl-shift-enter)?

Did you have any errors in any of those ranges?

wrote:

I used the array you suggested and I get this error:
#VALUE! Any ideas as to why I get this? I appreciate
your help

-----Original Message-----
If you really have the date in column C:

=SUM((MONTH(C13:C165)=3)*(L13:L165="Won")*(J13:J1 65))
(array entered, still)

And those ranges have to be the same size (I changed
L13:L196 to L13:L165).

This formula is equivalent, but it's not array
entered:

=SUMPRODUCT(--(MONTH(C13:C165)=3),--(L13:L165="Won"),
(J13:J165))

The double minuses convert trues/falses to +1/0's.

And =sumproduct() likes to work with numbers--not
logical values.

Mark wrote:

Hello,

Is it possible to sum up all contracts won in a
given
month? This is the information I enter into excel.
Kit#, Submit Date, Award Date, # of Kits, Price
Quoted,
Won / Lost. The date format is 4-Mar-97. I only
want
to
sum the awards that have Won in the Won / Lost
Colomn.
Here is the formula I used.{=SUM((C13:C165="Mar")*
(L13:L196="Won")*(J13:J165))}. Thanks in advance
for
your help.

Mark

--

Dave Peterson
.


--

Dave Peterson
.

.


--

Dave Peterson