Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Sum cells below a target call
I have a single column of data. There are several occurences of the
text "PAX" and always there is a number in the cell below. Is there a simple formula I can use to sum all these numbers up? Tried a sumif but no luck. e.g. PAX 6 4 2 PAX 10 answer should be 16 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Sum cells below a target call
One way:
=SUMIF(A1:A10,"Pax",A2:A11) Notice that the two ranges have the same number of cells, but the second range is offset by one row. ajnmx wrote: I have a single column of data. There are several occurences of the text "PAX" and always there is a number in the cell below. Is there a simple formula I can use to sum all these numbers up? Tried a sumif but no luck. e.g. PAX 6 4 2 PAX 10 answer should be 16 -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Sum cells below a target call
With
A1:A100 containing various values, or blanks, and wherever the word "PAX" exists, there is a number in the next cell down. and you want to sum those numbers. Try this: B1: =SUMIF(A1:A100,"PAX",A2) Note: A2, in the formula above, is 1 cell below A1. Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "ajnmx" wrote in message ... I have a single column of data. There are several occurences of the text "PAX" and always there is a number in the cell below. Is there a simple formula I can use to sum all these numbers up? Tried a sumif but no luck. e.g. PAX 6 4 2 PAX 10 answer should be 16 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Sum cells below a target call
Hi,
If the first instance of PAX is located in cell A1, another alternative is : =SUMIF(A1:A20,"PAX",OFFSET(A1,1,0)) so that, within the Offset, you can adjust as needed : 1 or 2 or 3 ... HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Several target cells in the solver | Excel Discussion (Misc queries) | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
Call Center Management: How to calculate 'cost per call' | Excel Discussion (Misc queries) | |||
Target Cells | Excel Worksheet Functions | |||
need a formula for setting colour target cells | Excel Worksheet Functions |