| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting when both match
Thanks it worked.
"Pete_UK" wrote: Assume your main data is in columns A and B, and suppose it covers row 1 to row 100. Use D1 for the start date (1/1/06), D2 for the end date (1/31/06) and enter this formula in D3: =SUM(IF(($A$1:$A$100="CompanyA")*($B$1:$B$100=D1) *($B$1:$B$100<=D2),1,0)) This is an array formula, so once you have typed it in (or when you subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula - you must not type these yourself. The formula will count the number of times Company A exists in column A with dates in column B which are between (and including) the dates in D1 and D2. Change it to "CompanyB" for that count. If you have dates which are outside the range, then the two counts will not match the total number of entries. Hope this helps. Pete |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Index | Excel Worksheet Functions | |||
| Counting Match Pairs In Rows | Excel Worksheet Functions | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Find a match that;s not exact | Excel Worksheet Functions | |||
| Vlookup, Index & Match | Excel Worksheet Functions | |||
Threaded Mode
