Sum using two criteria

[Mark]

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]

If you really have the date in column C:

=SUM((MONTH(C13:C165)=3)*(L13:L165="Won")*(J13:J165))
(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.

 

[Guest]

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

 

[Dave Peterson]

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

Did you have any errors in any of those ranges?

 

[Guest]

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

 

[Biff]

Hi!

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

Biff

 

[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))