| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif formula
Glad to hear it, Zak.
Pete On Jan 11, 1:49*pm, Zak wrote: Dont worry about it mate, i finally got it to work! thanks for all your help. "Pete_UK" wrote: To re-iterate - if you have a single condition to count, then use COUNTIF, but if you have 2 or more conditions you have to use something else. The "something else" will still give you a count of the items, like so: =SUMPRODUCT((condition_1)*(condition_2)*(condition _3)) even though it starts with the word "SUM". This will give you a count where 3 conditions apply. You haven't given any details of the columns you use, so assuming your dates are in column D, occupying D2:D50, then one of your conditions might be: (D2:D50=DATE(2008,1,1)) or if you put the start date in cell M2, for example, and the end date of your range in N2, then you would have something like: =SUMPRODUCT((D2:D50=M2)*(D2:D50<=N2)*(ISNUMBER(SE ARCH("GTS",B2:B50)))) This assumes that your line of service is in column B, and that you have 50 rows of data. You can adjust this if you have more, but it doesn't matter if you have less as the extra rows covered by the formula will not count anything. Hope this helps. Pete On Jan 11, 11:11 am, Zak wrote: I tried the countif with the * and it worked a treat, thanks for that. but how am i supposed to specify the date range witin the same formula? i know you said to use sum product or sumif but i have to use countif to count the GTS etc so have to use the date range within the same formula too. i want it to say if date is between 1-1-08 & 31-01-08 then return this... But then this means the formula would become outdated each month and it would have to be manually updated, is there not a way to automate this? Also, in the same sheet i am trying to record a macro so that is automates some manual tasks.. how can i tell the macro to recognise a cell range so that it recognises what to do regardless of whether the sheet has extra cells in it the next time. for example, if i copy the cell range j1:j32 because of what it contains what if next time it is a few extra cells or a few less. how would excel recognise what to do? thanks a lot. "Pete_UK" wrote: You can use wildcard characters in COUNTIF, so if you want to count all employees where the line of service begins with GTS, you can use "GTS*" as the criteria. You can only use COUNTIF where you have one condition - for multiple conditions (like including a date range), you will have to use SUMPRODUCT or SUM( IF( used as an array formula. Hope this helps. Pete On Jan 11, 9:33 am, Zak wrote: I want to use the countif function (or another that i may not be aware of) to count a certain number of things.. but the problem is i have many things i want it to count and the excel wizard does not let me add another argument, it appears the countif formula is just range and criteria with no additional extras. *If this is the case that an additional argument cannot be added is there another way to do the below? (without explaining what the below means) here it is what i am wanting some excel function (be it a macro or formula) to do: A large spreadsheet containing masses of data is used to analyse certain numbers for things. The spreadsheet is divided out by line of service for company and how many people work in each line of service. Each line of service is acronymed: GTS - INDIA, GTS - NEN, GTS - NEC, GTS - NES, GTS etc and this is not the end. it continues with a few more GTS.. and then also includes many other acronymed line of services. i want to use an excel function that will count the number of people in each line of service. Heres the problem: because the acronymed line of service name is not just GTS (bar one) but contains an additional few letters (as above) depending on region i cant seem to use the countif funtion to count everything above. the countif formula seems to just say "what is the criteria" so i put in GTS but this will obviously bring back everything just containing GTS and not GTS INDIA, GTS NEN etc. how do i do the formula/macro so that it can count everything i.e i should be able to say 'if includes' the letters GTS (so should bring back everything) as opposed to '=GTS' which only brings back GTS. Also is there a way to specify a date range within the formula? for example, i just want to know the number of people in all the different GTS fields in jan, feb etc. sorry about the long message. any help would be much appreciated.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help in countif formula. | Excel Discussion (Misc queries) | |||
| CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
| CountIf formula help | Excel Discussion (Misc queries) | |||
| countif formula | Excel Discussion (Misc queries) | |||
| Countif formula and then some... | Excel Worksheet Functions | |||
Threaded Mode
