Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
|
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
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 . . |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
boolean find criteria in Excel | Excel Discussion (Misc queries) |