Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting when both match
Here's where I need some help. I have a spreedsheet that will list off some
companyies like Company A, Company B. The next column has dates, like 1/25/06, 2/1/06. The formula needs to only count how many times that Company A has a date between 1/1/06 and 1/31/06. Then I'll repeat the formula for Company B. The 2 numbers have to match the total # of line entries in the worksheet. Please help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting when both match
Try something like this:
With your list in A1:B100 Whe Col_A contains comany names Col_B contains dates D1: (start date) E1: (end date) F1: Company_A F2: Company_B G1: =SUMPRODUCT(($A$1:$A$100=F1)*($B$1:$B$100=$D$1)*( $B$1:$B$100<=$E$1)) Copy that formula down to G2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Greg D" wrote: Here's where I need some help. I have a spreedsheet that will list off some companyies like Company A, Company B. The next column has dates, like 1/25/06, 2/1/06. The formula needs to only count how many times that Company A has a date between 1/1/06 and 1/31/06. Then I'll repeat the formula for Company B. The 2 numbers have to match the total # of line entries in the worksheet. Please help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting when both match
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 |
#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 |
Reply |
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 |