Sum using two criteria

  • Thread starter Thread starter Mark
  • Start date Start date
M

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
 
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.
 
I used the array you suggested and I get this error:
#VALUE! Any ideas as to why I get this? I appreciate
your help
 
Did you array enter it (ctrl-shift-enter)?

Did you have any errors in any of those ranges?
 
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))
 
Back
Top