Sum-Count-Offset-IF- Match

[QTE]

Hi Excel Forum,

I'm having difficulty using Sum, Count, Offset, IF, Match (and may b
Row) functions all together to give me the required result. Well,
think these are the correct functions? My syntax is a bit muddled!
No!...Wait! Don't go to the next Post: I'm sure there is a function
haven't used. Help!!

Scenario:

I have two columns. Column H has the data - all numerical values, usin
General Number Format. Column E will house the Formula /Results.

Data starts from Row 6

Column E - Result of Count - Required Results: 0, 1, 2, 3, 2, 5, 0(Row
6 - 12)

Column H - Data - Numbers Being Counted: 84, 33, 48, 100, 33, 52, 5
(Rows 6 -12)


1) Ordinary count of the values in column H, starting at row 6 whic
must return zero (0) in column E Row 6.
Formula Col E:
=COUNT(OFFSET($H$6:$H6,-1,0)) -----Row 6---Result 0
=COUNT(OFFSET($H$6:$H7,-1,0)) -----Row 7---Result 1
=COUNT(OFFSET($H$6:$H8,-1,0)) -----Row 8---Result 2
=COUNT(OFFSET($H$6:$H9,-1,0)) -----Row 9---Result 3

So far, so good.

2) However, I need to add IF, exact MATCH and SUM to the formula abov
starting in Row 6. When it gets to Row 10 value 33 is repeated, so wha
I need to say from the start is: Check IF any values MATCH. If value i
Column H MATCHes CURRENT Row being checked start count again and SU
COUNT - COUNT from the Row below first exactly MATCHed value which i
Row 8 to above the current value (33) being Matched which is Row 9
This will give a summed Count of 2.

=SUM(IF(MATCH($H6,$H$6:$H9),0 ? + the count + offset above?

Result in Column E Row 11 = 5 (value 52 count = 5)
Result in Column E Row 12 = 0 because value 52 was Repeated and MATCHe
the current Row being checked - Row 12 (Column E with Formula) and s
Summed Count would be Column H Row 12 to Column H Row 12 with Offset o
-1.

Working example greatly appreciated.

Kind regards
QT

 

[Frank Kabel]

Hi
try the following (using a helper column 'X'):
X6: Enter the following array formula (enetred with CTRL+SHIFT+ENTER):
=MAX(IF($H$5:$H5=H6,ROW($H$5:$H5)))

(you may hide this column)
E6: Enter the formula
=IF(COUNTIF($H$5:$H5,H6),COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

copy both formulas down

 

[QTE]

Hi Frank,

Thank you very much for your assistance: formula's work great.
However, could I trouble you further to explain the INDIRECT reference
used in the formula. I've checked out the Excel Help on INDIRECT an
so have a vague idea but am still unsure of the referencing in th
formula - can you please explain what this part of the formula i
actully doing:

COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

Also, what is the ampersand & doing?

*Hi
try the following (using a helper column 'X'):
X6: Enter the following array formula (enetred wit
CTRL+SHIFT+ENTER):
=MAX(IF($H$5:$H5=H6,ROW($H$5:$H5)))

(you may hide this column)
E6: Enter the formula
=IF(COUNTIF($H$5:$H5,H6),COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

copy both formulas down

*[/QUOTE]

Kind regards,
QT

 

[Frank Kabel]

Hi
first the easies part
The ampersand '&' is the same as the function CONCATENATE. That is it
combines strings.

INDIRECT: this function is used to create a reference based on a STRING
.. So within this function I create a string using the ampersand:
("H" & X6+1 & ":H" &ROW())

This will evaluate to a reference like
H[calculated_starting_row]:H[current_row]

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

Thank you very much for your assistance: formula's work great.
However, could I trouble you further to explain the INDIRECT
references used in the formula. I've checked out the Excel Help on
INDIRECT and so have a vague idea but am still unsure of the
referencing in the formula - can you please explain what this part of
the formula is actully doing:

COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

Also, what is the ampersand & doing?

*

Kind regards,
QTE
[/QUOTE]

 

[QTE]

Hi Frank,

Sorry, I still haven't quite got it:

COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

I'm confused with the INDIRECT referencing to calculate the start Row
Could you spare another attempt?

*Hi
first the easies part
The ampersand '&' is the same as the function CONCATENATE. That is i
combines strings.

INDIRECT: this function is used to create a reference based on
STRING
.. So within this function I create a string using the ampersand:
("H" & X6+1 & ":H" &ROW())

This will evaluate to a reference like
H[calculated_starting_row]:H[current_row]

--
Regards
Frank Kabel
Frankfurt, Germany

*

The reference to the string calculating the starting row:
"H" & X6+1
Why does the helper column/row X6 need to be referenced with +1?
Is this the same +1 that is deducted later from the ROW() count:
COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

Kind regards,
QT

 

[Frank Kabel]

Hi
the start row is calculated in the helper column X (with the array
formula). INDIRECT just uses this calculated cell value

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

Sorry, I still haven't quite got it:

COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

I'm confused with the INDIRECT referencing to calculate the start Row.
Could you spare another attempt?

*Hi
first the easies part
The ampersand '&' is the same as the function CONCATENATE. That is it
combines strings.

INDIRECT: this function is used to create a reference based on a
STRING
.. So within this function I create a string using the ampersand:
("H" & X6+1 & ":H" &ROW())

This will evaluate to a reference like
H[calculated_starting_row]:H[current_row]

--
Regards
Frank Kabel
Frankfurt, Germany

*

The reference to the string calculating the starting row:
"H" & X6+1
Why does the helper column/row X6 need to be referenced with +1?
Is this the same +1 that is deducted later from the ROW() count:
COUNT(INDIRECT("H" & X6+1 & ":H" &
ROW()))-1,COUNT($H$5:$H5))

Kind regards,
QTE

 

[QTE]

Hi Frank,

Thank you for clarification. I need to play with the INDIRECT Functio
some more. Much appreciated.

*Hi
the start row is calculated in the helper column X (with the array
formula). INDIRECT just uses this calculated cell value

--
Regards
Frank Kabel
Frankfurt, Germany

*

Kind regards,
QT