Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria Range result plus x amt of cells
I would like to use each true criteria range result as a reference to find
the cell contents to sum. i.e.True Criteria range result found in C10, Add contents found in cell C17. The cell resulting in "true" from the criteria will be 7 cells above the amount required for SUM ? Can I reference the results of the criteria range and criteria formula as a base location to find the cell with the amount to be summed ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria Range result plus x amt of cells
Try, in any cell other than in col C, say in D2:
=SUMIF(C:C,TRUE,OFFSET(C1,ROWS($1:1)*7,)) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Elaine" wrote: I would like to use each true criteria range result as a reference to find the cell contents to sum. i.e.True Criteria range result found in C10, Add contents found in cell C17. The cell resulting in "true" from the criteria will be 7 cells above the amount required for SUM ? Can I reference the results of the criteria range and criteria formula as a base location to find the cell with the amount to be summed ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria Range result plus x amt of cells
Hi,
If you show us a sample of the data and the result it may helps us. Cheers, Shane Devenshire "Elaine" wrote: I would like to use each true criteria range result as a reference to find the cell contents to sum. i.e.True Criteria range result found in C10, Add contents found in cell C17. The cell resulting in "true" from the criteria will be 7 cells above the amount required for SUM ? Can I reference the results of the criteria range and criteria formula as a base location to find the cell with the amount to be summed ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria Range result plus x amt of cells
OK, I'm trying that but its not giving me the right answer, Using H column
for one criteria and J column for the criteria which I'd like to use as a reference, where in the formula am I putting your suggestion...is it the sum_range? Alternatively, the number I need also appears in Column L, 5 rows beneath the criteria found at J. "Max" wrote: Try, in any cell other than in col C, say in D2: =SUMIF(C:C,TRUE,OFFSET(C1,ROWS($1:1)*7,)) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Elaine" wrote: I would like to use each true criteria range result as a reference to find the cell contents to sum. i.e.True Criteria range result found in C10, Add contents found in cell C17. The cell resulting in "true" from the criteria will be 7 cells above the amount required for SUM ? Can I reference the results of the criteria range and criteria formula as a base location to find the cell with the amount to be summed ? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria Range result plus x amt of cells
First, my apologies. The earlier expression should be just:
=SUMIF(C:C,TRUE,OFFSET(C1,7,)) What it does is simply to sum all cells in col C which are 7 cells down from where TRUE is evaluated (or entered) within the same col C. That's my interp based on your original post, ref your lines: .. use each true criteria range result as a reference to find the cell contents to sum. i.e.True Criteria range result found in C10, Add contents found in cell C17 .. Here's a quick test file to illustrate the above for easy recap: http://freefilehosting.net/download/42b4b Sum values in same col 7 cells down where TRUE in col.xls Trust the above answers/closes what you asked for in your original post. [Please click the YES button below to acknowledge] I'm not sure about your "new" query below? Looks like you're changing/expanding the scenario? Suggest that you put in a fresh new posting. Elaborate it with your sample data (what you have set up), the required logic (what you want to happen) and expected results (where you want the results to appear). -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Elaine" wrote: OK, I'm trying that but its not giving me the right answer, Using H column for one criteria and J column for the criteria which I'd like to use as a reference, where in the formula am I putting your suggestion...is it the sum_range? Alternatively, the number I need also appears in Column L, 5 rows beneath the criteria found at J. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count a range of cells from another columns criteria | Excel Worksheet Functions | |||
Counting cells in a range per multiple criteria . . . | Excel Worksheet Functions | |||
Sum.if criteria related to range of cells | Excel Worksheet Functions | |||
color a range of cells based on a criteria | Excel Discussion (Misc queries) | |||
Copy range of cells omitting formulas that result in " " | Excel Discussion (Misc queries) |