Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumif with a twist?
43975000 50
43865000 100 43828000 5 43828001 21 Assume the block of data above, and that I want to do a sumif formula, with the range being the 1st column, but only the first 5 characters of said cells in the range. Example, the criteria will be 43828 and I want the result to be the value 26. The reason is this is a basically a rollup of values in column B when the match is on the first 5 characters. Does this make sense? Thanks, |
#2
|
|||
|
|||
If the figures in A2:A10 are true numbers and F2 houses the number 43828:
=SUMPRODUCT(--(LEFT($A$2:$A$10&"00000",5)+0=F2),$B$2:$B$10) If the figures in A2:A10 are text-formatted number and F2 as befo =SUMIF($A$2:$A$10,F2&"*",$B$2:$B$10) Bruce wrote: 43975000 50 43865000 100 43828000 5 43828001 21 Assume the block of data above, and that I want to do a sumif formula, with the range being the 1st column, but only the first 5 characters of said cells in the range. Example, the criteria will be 43828 and I want the result to be the value 26. The reason is this is a basically a rollup of values in column B when the match is on the first 5 characters. Does this make sense? Thanks, -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |