Offset, Dynamic range, Countif
 

I need to count the # specific items within a list of data (in Column K).
Column K data is limited to four "Status" responses ("Confirmed" "Good" "Bad"
"Non-Responsive"). The corresponding dates (in Column J) tells me the date
these items were entered into system.

I would like to have a formula which looks at the date within cell A3 (a
variable input cell allowing me to search using a

specific date) and looks only within the date range under column J and
counts the # of "Non-Responsive" cells under column K (still abiding by that
date cell range).

I have tried many diff ways but am having trouble utilizing the offset
function (I've never used it). Please help me out guys.

Offset, Dynamic range, Countif
 

Hi Bryce

One way
=SUMPRODUCT(--($J$2:$J$1000=$A$3),--($K$2:$K$1000="Non-Responsive"))
or, if you put the status in another cell (A4?) then you could get each of
the results without changing the formula, just by changing A4
=SUMPRODUCT(--($J$2:$J$1000=$A$3),--($K$2:$K$1000=A4))

Change ranges to suit, but do ensure that they are of equal length.

Regards

Roger Govier


Bryce wrote:
I need to count the # specific items within a list of data (in Column K).
Column K data is limited to four "Status" responses ("Confirmed" "Good" "Bad"
"Non-Responsive"). The corresponding dates (in Column J) tells me the date
these items were entered into system.

I would like to have a formula which looks at the date within cell A3 (a
variable input cell allowing me to search using a

specific date) and looks only within the date range under column J and
counts the # of "Non-Responsive" cells under column K (still abiding by that
date cell range).

I have tried many diff ways but am having trouble utilizing the offset
function (I've never used it). Please help me out guys.

Offset, Dynamic range, Countif
 

Something along the lines of:

=SUMPRODUCT(($J$2:$J$31=$A$3)*($K$2:$K$31="Non-Responsive"))

You could put Non-Responsive into A4 and this would then read:

=SUMPRODUCT(($J$2:$J$31=$A$3)*($K$2:$K$31=$A$4))

HTH

"Bryce" wrote:

I need to count the # specific items within a list of data (in Column K).
Column K data is limited to four "Status" responses ("Confirmed" "Good" "Bad"
"Non-Responsive"). The corresponding dates (in Column J) tells me the date
these items were entered into system.

I would like to have a formula which looks at the date within cell A3 (a
variable input cell allowing me to search using a

specific date) and looks only within the date range under column J and
counts the # of "Non-Responsive" cells under column K (still abiding by that
date cell range).

I have tried many diff ways but am having trouble utilizing the offset
function (I've never used it). Please help me out guys.

Offset, Dynamic range, Countif
 

Hi

=SUMPRODUCT(--($K$2:$K$1000="Non-Responsive"),--($J$2:$J$1000=$A$3))


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"Bryce" wrote in message
...
I need to count the # specific items within a list of data (in Column K).
Column K data is limited to four "Status" responses ("Confirmed" "Good"
"Bad"
"Non-Responsive"). The corresponding dates (in Column J) tells me the date
these items were entered into system.

I would like to have a formula which looks at the date within cell A3 (a
variable input cell allowing me to search using a

specific date) and looks only within the date range under column J and
counts the # of "Non-Responsive" cells under column K (still abiding by
that
date cell range).

I have tried many diff ways but am having trouble utilizing the offset
function (I've never used it). Please help me out guys.