Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a total based on criteria in two columns
Hi,
I am trying to get a total number of records based on criteria in two different columns. The columns a Date closed (data enetered as YYYY/MM/DD) and status (where I am looking for "CLOSED", "REGISTERED" and "RENEWED"). I want to count the number of files that have been closed, registered and renewed in a specific month of a specific year, the day is irrelivant. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a total based on criteria in two columns
There may be an easier formula but this worked for me. {=SUM(IF(MONTH(A$2:A$7)=1,IF(YEAR(A$2:A$7)=2005,IF (B$2:B$7=I2,1,0))))} Range A2:A7 is the date in YYYY/MM/DD format. Range B2:B7 is the "Status" (REGISTERED, CLOSED, RENEWED) I chose the month of January and the year of 2005 to be the count criteria. In the column to the right of my formula, I entered the 3 statuses in cells I2:I4. Formula pasted in cell H2 and dragged down to H4 to reference each status. This is an array formula so enter with Ctrl-Shift-Enter. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487191 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
get a total based on criteria in two columns
=SUMPRODUCT(--(TEXT(A2:A200="yyyymmm")="2005Jan"),--(B2:B200="CLOSED"))
etc. -- HTH RP (remove nothere from the email address if mailing direct) "rar" wrote in message ... Hi, I am trying to get a total number of records based on criteria in two different columns. The columns a Date closed (data enetered as YYYY/MM/DD) and status (where I am looking for "CLOSED", "REGISTERED" and "RENEWED"). I want to count the number of files that have been closed, registered and renewed in a specific month of a specific year, the day is irrelivant. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help w/ counting multiple columns based on IF criteria | Excel Worksheet Functions | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
Is it possible to use the sumif function based on the criteria of. | Excel Worksheet Functions | |||
how do i count values based on multiple criteria | Excel Worksheet Functions | |||
returning a value based on mulitple criteria | Excel Worksheet Functions |