Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Last cell used in SUM IF formula
My data set varies in length and would like my SUM IF formula to always pick
that out. Id like to eliminate going into the formula and changing the 5000. There are never any blanks. Im using: =SUM(IF(('Export'!$F$3:$F$5000="Event"),'Export'!$ P$3:$P$5000))¦ array entered. And can find the last used cell, =MATCH(REPT("z",255),F:F)¦ last used cell in column F but cannot get correct syntax to merge the two formulae. Any help greatly appreciated. |
#2
|
|||
|
|||
Last cell used in SUM IF formula
Here is an example that you can adapt
=SUM(INDIRECT("D1:D"&MAX(MATCH(9.99999999999999E+3 07,F:F)))) -- HTH RP (remove nothere from the email address if mailing direct) "GerryK" wrote in message ... My data set varies in length and would like my SUM IF formula to always pick that out. I'd like to eliminate going into the formula and changing the 5000. There are never any blanks. I'm using: =SUM(IF(('Export'!$F$3:$F$5000="Event"),'Export'!$ P$3:$P$5000)). array entered. And can find the last used cell, =MATCH(REPT("z",255),F:F). last used cell in column F but cannot get correct syntax to merge the two formulae. Any help greatly appreciated. |
#3
|
|||
|
|||
Last cell used in SUM IF formula
Correction
=SUM(INDIRECT("F3:F"&MAX(MATCH(9.99999999999999E+3 07,F:F)))) -- HTH RP (remove nothere from the email address if mailing direct) "GerryK" wrote in message ... My data set varies in length and would like my SUM IF formula to always pick that out. I'd like to eliminate going into the formula and changing the 5000. There are never any blanks. I'm using: =SUM(IF(('Export'!$F$3:$F$5000="Event"),'Export'!$ P$3:$P$5000)). array entered. And can find the last used cell, =MATCH(REPT("z",255),F:F). last used cell in column F but cannot get correct syntax to merge the two formulae. Any help greatly appreciated. |
#4
|
|||
|
|||
Last cell used in SUM IF formula
Another way is to name your data.
Type in a header (say data_f) at F2, put the cursor on the bottom border of the cell and SHIFT+double click left mouse. This will select all the data in column F. Insert Name Create Top Row Change your formula to SUM(data_f) If you insert or delete cells in your named range, other than the top or bottom cell, the named range adjusts automatically. No need to change the formula. Otherwise, rename the range with the above method, or implement a dynamic range name. Search this group. Tip: To move to the top or bottom of a range, double click the top or bottom border of any cell. |
#5
|
|||
|
|||
Last cell used in SUM IF formula
As this formula
=SUM(IF(('Export'!$F$3:$F$5000="Event"),'Export'!$ P$3:$P$5000)) shows, you have a single condition. Therefore a SumIf formula, which is faster, will suffice: =SUMIF(Export!$F$3:$F$65536,"Event",Export!$P$3:$P $65536) If you have more conditions... Define Lrec as referring to: =MATCH(REPT("z",255),Export!$F$3:$F$65536) or as referring to: =MATCH(9.99999999999999E+307,Export!$P$3:$P$65536) which you can use in a formula like: =SUMPRODUCT(--(Export!$F$3:INDEX(Export!$F$3:$F$65536,Lrec)="Eve nt"),...,Export!$P$3:INDEX(Export!$P$3:$P$65536,Lr ec)) If you were on Excel 2003, all this machinery can be avoided. GerryK wrote: My data set varies in length and would like my SUM IF formula to always pick that out. Id like to eliminate going into the formula and changing the 5000. There are never any blanks. Im using: =SUM(IF(('Export'!$F$3:$F$5000="Event"),'Export'!$ P$3:$P$5000))¦ array entered. And can find the last used cell, =MATCH(REPT("z",255),F:F)¦ last used cell in column F but cannot get correct syntax to merge the two formulae. Any help greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any cell containing formula seen as data instead of formula | Excel Worksheet Functions | |||
Using contents of a cell in a formula | Excel Discussion (Misc queries) | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions | |||
looking for a formula | Excel Worksheet Functions |