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, |
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. |
All times are GMT +1. The time now is 06:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com