countif using array

[hallhv01]

OK, my last question was how to count only if two columns were the specified value. The array function worked - thanks: {=SUM(IF($F$2:$F$30="s-j",IF($L$2:$L$30=TRUE,1,0),0))}. I have copied this formula to count all the s-j, s-e, c-j, c-e, etc. in separate totals.

So now I have a third column, I will call it column A, that has dates ordered decending mm/dd/yy.
I have a cell in this model, I will call it C1, where I can type in a number, formated as a % that is to indicate the total % of all s-e, s-j, c-e, c-j etc. with true to be counted (formula above). This would be simple (C1*each total), but it is the top X% by date (column A), that need to be counted. So we could have all the 20% in one category, say s-e, if those were the top 20% of the dates, then the totals for S-j and so on should be blank. The end result needs to be the original groupings of totals I had from the formula above, but with only the top X% by date counted. So essentially it is adding one more componet to the count function above, to only count if the date is in the top X% if it is s-e etc. in one colunm, and true in the other.
I hope this made sense.
Thanks!!

OK, here is some clarification, in the responses I am seeing so far, I think there is a set date in the formulas, in fact the date is variable, some top percentage. I need to count all s-j's for example in one colunm, only if another column is true, I do the same thing over for s-e, c-e, c-j ect using {=SUM(IF($F$2:$F$30="s-e",IF($L$2:$L$30=TRUE,1,0),0))}. Then the date column comes in. I need the above, but count only if the date is in the top 20%, or whatever % I choose. The dates range from the 1960s to the present, none are the same, I need to have it count only the top ?%.
Example:
So I should not count s-j, true, 3/3/76 if it is not in the top (say 30%) of the dates, but I should count it if it is in the top 30% of the dates.
The spreadsheet is sorted by date in decending order. Maybe there is a way to tell it based on how many rows to only apply my formula to the top X percent of the rows?


[ This Message was edited by: hallhv01 on 2002-05-20 13:07 ]

[Dave Patton]

I am not really sure of your question.
You can modify the following.
It checks your 2 criteria and counts them if they are in a particular date range. I used number instead of date format since people use a variety of date formats.

=SUMPRODUCT((F2:F30="s-j")*(L2:L30=TRUE)*(A2:A30>37380)*(A2:A30<37400))

[NateO]

You can use this:

Microsoft Excel - Book1_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1		=	s-j
*	A	B	C	D
1	s-j	TRUE	12/31/2002	:alert("{=SUM((A1:A33="s-j")*(B1:B33=TRUE)*(C1:C33=DATEVALUE("12/31/2002")))}")>2
2	s-j	TRUE	12/31/2002	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Where the formula under 2 is:

{=SUM((A1:A33="s-j")*(B1:B33=TRUE)*(C1:C33=DATEVALUE("12/31/2002")))}