countif functions -> please help

  • Thread starter Thread starter miller
  • Start date Start date
M

miller

I am using a sumif function to collect data from an entire range but wa
wondering if there is any way (without manually changing the range) t
only sum the last 10 entries.

The formula as it reads now is:

=SUM(IF(B2:B29="H",IF(I2:I29="W",1,0)))

I only want the last 10 values that fit this criteria and I don't wan
to have to change the range since I add new values everyday and I hav
30 workbooks.

Any help would be much appreciated
 
One way:

=SUMPRODUCT(--(OFFSET(B2,COUNTA(B2:B1000)-10,,10,1)="H"),
--(OFFSET(I2,COUNTA(B2:B1000)-10,,10,1)="W"))

This assumes at least 10 values from B2:B11 and no blanks below B1.
Adjust 1000 as necessary to extend beyond your data range.
 
Thanks for the reply but it doesn't work the way I want it to. Is ther
any chance I could email the sheet for you to look at?
 
You'd be better off posting a more complete explanation to the
newsgroup - there are a lot of people who can help, many of whom
know more about XL than I do.

I take it from rereading your original post that you want to only
sum the last 10 entries that have an H in column B and a W in column
I. Is that correct?

If so, what's confusing is that you current formula would lead one
to believe that you're adding 1 for each occurrence. If that's the
case, then the sum of the last 10 occurrences will always be 10,
right? Or perhaps, if there may be less than 10 occurrences:

=MIN(10,SUMPRODUCT(--(B2:B65536="H"),--(I2:I65536="W"))
 
Sorry for the confusion. This is what I want to do:

Example:
The workbook has the colums A and B with the values underneath.

_A___B_
H W
A L
H L
H W
H W
A L
A W
H L
H W
A L

I would like to have a formula that is the entire range of data (10
rows) but only to count the most recent (bottom values) of H. I want it
to count in the last 3 occurences of H how many times in the B column
there is a W. In the above example it would be 2. Of the most recent 3
of the 6 H's in column A there was a coresponding W in column B 2
times. I want to be able to insert data after the last column and have
the formula give the correct number occurences.

If I was to insert a new row with the values H L I want the formula to
update and give the answer 1 since the last 3 H's in the A column have
the corresponding L, W, L values.

If it is clear like mud I apologize but thank you for your help.
 
Sorry for the confusion. This is what I want to do:

Example:
The workbook has the colums A and B with the values underneath.

_A___B_
H W
A L
H L
H W
H W
A L
A W
H L
H W
A L

I would like to have a formula that is the entire range of data (10
rows) but only to count the most recent (bottom values) of H. I want it
to count in the last 3 occurences of H how many times in the B column
there is a W. In the above example it would be 2. Of the most recent 3
of the 6 H's in column A there was a coresponding W in column B 2
times. I want to be able to insert data after the last column and have
the formula give the correct number occurences.

If I was to insert a new row with the values H L I want the formula to
update and give the answer 1 since the last 3 H's in the A column have
the corresponding L, W, L values.

If it is clear like mud I apologize but thank you for your help.
Click to expand...

Try the *array-entered* formula:

=SUM((T(OFFSET(B1,LARGE((A1:A20="H")*ROW(A1:A20),{1,2,3})-1,))="W")*1)

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

Set the range A1:A20 to represent the range in which you might possibly have
data: e.g. A2:A1000.

Oh, when you say "insert a new row", do you really mean "insert" as using the
"Insert" menu to do so? If that is what you mean, then the formula can
"self-adjust".


--ron
 
Back
Top