Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
I have a grid where the column data is as follows
A B C D E Exclude; Status; Property Address; Enter Date; Price I want formulas to calculate the following 1) Count the rows where (Exclude is empty) and (Status = 'ACT') 2) Count the rows where (Exclude is empty) and (Status = 'ACT') and (Enter Date is within the last 6 months) 3) Average the Price where (Exclude is empty) and (Status = 'ACT') Thanks for any help Mary ZZZ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
Try:
(1) =SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT")) or =SUMPRODUCT(--($A$2:$A$10=""),--($B$2:$B$10="ACT")) (2) ... I have taken 6 months to be 183 days. =SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT"),--(C2:C10=TODAY()-183)) (3) =SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT")),(E1:E10)/SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT")) HTH "MaryZZZ" wrote: I have a grid where the column data is as follows A B C D E Exclude; Status; Property Address; Enter Date; Price I want formulas to calculate the following 1) Count the rows where (Exclude is empty) and (Status = 'ACT') 2) Count the rows where (Exclude is empty) and (Status = 'ACT') and (Enter Date is within the last 6 months) 3) Average the Price where (Exclude is empty) and (Status = 'ACT') Thanks for any help Mary ZZZ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
By Searching the Group, I figured out how to do all the formulas and
conditions except for the following I want to count the rows that are within the last 6 months of today. Column D has a date or is empty. So if TODAY() is 3/30/2007, I want to count all the rows where D 9/30/2006. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
Made a mistake in my previous post. Dawns on me that as time passes,
soon I will not have any rows selected if I compare dates to TODAY(). The data is static and TODAY() is not. I suppose I need to add a "REPORT Date" to a cell on the spreadsheet, and count the rows that are GT ("REPORT Date" - 6 months). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
=SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT"),--(C2:C10=X1-183))
where X1 is "Report date" "MaryZZZ" wrote: Made a mistake in my previous post. Dawns on me that as time passes, soon I will not have any rows selected if I compare dates to TODAY(). The data is static and TODAY() is not. I suppose I need to add a "REPORT Date" to a cell on the spreadsheet, and count the rows that are GT ("REPORT Date" - 6 months). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
I have a similar problem, however, I would like to ask it to look for
conditions based on month of occurrence. So, I wish to know how many occurrences were initiated by a certain department (column f) and if the occurrence was rejected (column g), then which of the rejected occurrences for that specific department happened in june, july, etc. (column a). I have come up with some very complicated formulas, however the answers they return don't jive when put to the test. Any help would be greatly appreciated as I have spent the better part of a day working on this . . . "Toppers" wrote: =SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT"),--(C2:C10=X1-183)) where X1 is "Report date" "MaryZZZ" wrote: Made a mistake in my previous post. Dawns on me that as time passes, soon I will not have any rows selected if I compare dates to TODAY(). The data is static and TODAY() is not. I suppose I need to add a "REPORT Date" to a cell on the spreadsheet, and count the rows that are GT ("REPORT Date" - 6 months). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
Try something like this:
=SUMPRODUCT(--(MONTH(A1:A10)=6),--(F1:F10="DeptA"),--(G1:G10="Rejected")) Biff "Lee" wrote in message ... I have a similar problem, however, I would like to ask it to look for conditions based on month of occurrence. So, I wish to know how many occurrences were initiated by a certain department (column f) and if the occurrence was rejected (column g), then which of the rejected occurrences for that specific department happened in june, july, etc. (column a). I have come up with some very complicated formulas, however the answers they return don't jive when put to the test. Any help would be greatly appreciated as I have spent the better part of a day working on this . . . "Toppers" wrote: =SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT"),--(C2:C10=X1-183)) where X1 is "Report date" "MaryZZZ" wrote: Made a mistake in my previous post. Dawns on me that as time passes, soon I will not have any rows selected if I compare dates to TODAY(). The data is static and TODAY() is not. I suppose I need to add a "REPORT Date" to a cell on the spreadsheet, and count the rows that are GT ("REPORT Date" - 6 months). |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing multiple column values
=SUMPRODUCT(--($F$2:$F$10="Dept"),--($G$2:$G$10="Rejected"),--MONTH($A$2:$A$10)=6)))
Set "Dept" to Department ID, "Rejected" to Reject code/text. You could put these values in a cell(s) and change formula ... =SUMPRODUCT(--($F$2:$F$10=X1),--($G$2:$G$10=X2),--MONTH($A$2:$A$10)=6))) This will results for Jume (month=6) It assumes only data for one year e.g. 2007. If there there is data for more than one year: =SUMPRODUCT(--($F$2:$F$10="Dept"),--($G$2:$G$10="Rejected"),--MONTH($A$2:$A$10)=6)),--YEAR($A$2:$A$10)=2007) to give data for a given year. You could make a table as shown below and use the SUMPRODUCT formula in the table: Jun Jul Aug Sep Dept A B C The dates would be formatted using acual dates e.g. o1/06/07 (UK dd/mm/yy) Substitute the cell address of a date for the value 6 and the address of Dept for "Dept" Also look at Pivot Table as another way of presenting the data. HTH "Lee" wrote: I have a similar problem, however, I would like to ask it to look for conditions based on month of occurrence. So, I wish to know how many occurrences were initiated by a certain department (column f) and if the occurrence was rejected (column g), then which of the rejected occurrences for that specific department happened in june, july, etc. (column a). I have come up with some very complicated formulas, however the answers they return don't jive when put to the test. Any help would be greatly appreciated as I have spent the better part of a day working on this . . . "Toppers" wrote: =SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT"),--(C2:C10=X1-183)) where X1 is "Report date" "MaryZZZ" wrote: Made a mistake in my previous post. Dawns on me that as time passes, soon I will not have any rows selected if I compare dates to TODAY(). The data is static and TODAY() is not. I suppose I need to add a "REPORT Date" to a cell on the spreadsheet, and count the rows that are GT ("REPORT Date" - 6 months). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Multiple values to get lowest Value | Excel Worksheet Functions | |||
comparing 2 values on one column in chart | Charts and Charting in Excel | |||
To find Multiple values in column B for a unique value in column A | Excel Worksheet Functions | |||
Comparing multiple cell values | Excel Discussion (Misc queries) | |||
comparing column values | Excel Worksheet Functions |