 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2006How do I set up a countif, or sumif that is multiconditional?
I often try to set up a countif or a sum if that is conditional on two or more conditions. For instance - I have Radio stations listed in one column, flight dates in another column, number of contacts for each flight in another column and appointments created from those contacts and finally, revenue generated form those appointments. One example would be to sumif column one equals the radio station and column two equaled the flight date then give me the revenue. Hy Yanni try to use =sumproduct(--($a$2:$a$100="radio")*($B$2:$b$100=date)*($c$2:$c$100)) assuming that the revenue are on the C column hope this helps regards from Brazil Marcelo > I often try to set up a countif or a sum if that is conditional on two or > more conditions. For instance - I have Radio stations listed in one column, [quoted text clipped - 3 lines] > equals the radio station and column two equaled the flight date then give me > the revenue. > Hy Yanni > [quoted text clipped - 14 lines] > > equals the radio station and column two equaled the flight date then give me > > the revenue. I did not know such a function existed. I will give it a try. Thank you my brazilian friend. gracious. You are welcome Mr Yanni > > Hy Yanni > > [quoted text clipped - 17 lines] > I did not know such a function existed. I will give it a try. Thank you my > brazilian friend. gracious. > Hy Yanni > [quoted text clipped - 14 lines] > > equals the radio station and column two equaled the flight date then give me > > the revenue. Just tried it out - it works. Hi Take a look at the Sumproduct() function =SUMPRODUCT(--($A$1:$A$100="Station1"),--($B$1:$B$100=DATE(2006,6,20)),$C$1:$C$100) This assumes that your Radio Station is in column A, your Flight Dates in column B and your Revenue in column C. Change ranges to suit, but do ensure that the ranges are of equal size. Substitute for Station 1, the name of the station you want and for the date (example shows 20th June 2006)  SignatureRegards Roger Govier >I often try to set up a countif or a sum if that is conditional on two >or [quoted text clipped - 9 lines] > give me > the revenue. > Hi > [quoted text clipped - 21 lines] > > the revenue. > Ok, it seems that the final array automatically sums under these conditions...How can I use the same formula to countif the first two or more conditions exist? Hi! I have a similar query, i know how to use the sumproduct function in rgard to my data, what i want to know is how to refer to column in another sheet. Thanks Hi Jess Just prefix the range with the sheet name and an exclamation mark e.g. Sheet1!$A$1:$A$100 'My Data'!$A$1:$A$100 Note that if you have spaces in the sheet name, you must enclose the name within single quotes. SignatureRegards Roger Govier > Hi! I have a similar query, i know how to use the sumproduct function > in > rgard to my data, what i want to know is how to refer to column in > another > sheet. > Thanks Hi Mr Yanni >> Ok, it seems that the final array automatically sums under these >> conditions...How can I use the same formula to countif the first two >> or more conditions exist? Just leave out the final Revenue range =SUMPRODUCT(--($A$1:$A$100="Station1"),--($B$1:$B$100=DATE(2006,6,20))) SignatureRegards Roger Govier >> Hi >> [quoted text clipped - 30 lines] >> conditions...How can I use the same formula to countif the first two >> or more conditions exist? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.