Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find the last row with data
I have an Inventorytab called INV1. I dump raw data in there. Some months
it will contain just 4,000 lines of data or up to 51,000 lines of data. My second tab is a Calculator tab and it looks at data in the INV1 tab and does a summary. =COUNT(IF(Inv1!$A$2:$A$65536=A3,IF(Inv1!$C$2:$C$65 536=B3,IF(Inv1!$E$2:$E$65536=C3,0)))) ....(this is an array) Is there a way of putting something in the above formula to look at just the rows with data? The way I have my formula now means excel looks at row 2 to 65536 even though a lot of rows are blank Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find the last row with data
You can create a dynamic range.
Goto InsertNameDefine Name: rng Refers to: =OFFSET(Inv1!$A$2:$E$2,,,COUNTA(Inv1!$A$2:$A$65536 )) Then, your formula becomes (still an array): =COUNT(IF(INDEX(rng,,1)=A3,IF(INDEX(rng,,3)=B3,IF( INDEX(rng,,5)=C3,0)))) Or (still an array): =SUM((INDEX(rng,,1)=A3)*(INDEX(rng,,3)=B3)*(INDEX( rng,,5)=C3)) -- Biff Microsoft Excel MVP "Poppy" wrote in message ... I have an Inventorytab called INV1. I dump raw data in there. Some months it will contain just 4,000 lines of data or up to 51,000 lines of data. My second tab is a Calculator tab and it looks at data in the INV1 tab and does a summary. =COUNT(IF(Inv1!$A$2:$A$65536=A3,IF(Inv1!$C$2:$C$65 536=B3,IF(Inv1!$E$2:$E$65536=C3,0)))) ....(this is an array) Is there a way of putting something in the above formula to look at just the rows with data? The way I have my formula now means excel looks at row 2 to 65536 even though a lot of rows are blank Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOw do i find and sort out data repeats for specific data set | Excel Discussion (Misc queries) | |||
Find formatting doesn't work: "Excel cannot find data" | Excel Discussion (Misc queries) | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) | |||
FIND DATA WITHIN DATA (V-OR-H LOOKUP/FIND/MATCH?) | Excel Worksheet Functions |