Comparing multiple column values

[MaryZZZ]

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

 

[Guest]

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]

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.

 

[MaryZZZ]

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).

 

[Guest]

=SUMPRODUCT(--(ISBLANK($A$2:$A$10)),--($B$2:$B$10="ACT"),--(C2:C10>=X1-183))

where X1 is "Report date"

 

[Guest]

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 . . .

 

[T. Valko]

Try something like this:

=SUMPRODUCT(--(MONTH(A1:A10)=6),--(F1:F10="DeptA"),--(G1:G10="Rejected"))

Biff

 

[Guest]

=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