Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format - Formula to Colour Every 3rd Cell in Offset Range | Excel Discussion (Misc queries) | |||
Chart attached to dynamic range | Charts and Charting in Excel | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Reference to a dynamic range | Excel Discussion (Misc queries) | |||
Add a Dynamic Range with 2 Conditions Q | Excel Worksheet Functions |