SUMIFS Formula with LEFT Function

Hello,

can anybody pls help me..

i have this formula.. and i need help here..

=SUMIFS(Sheet2!$D$2847:$D$3065;Sheet2!$I$2847:$I$3065;$B$6;Sheet2!$G$2847:$G$3065;$C$6;Sheet2!$B$2847:$B$3065;A14)


D2847 - D3065

includes Numbers..

lets say.. i want every cell counted which has in these range the first two numbers..

every cell has..

5052
5053
5054
4060
4050

so i count the entries which has 50.. so in total it gives a result of 3. Not summing them, just count the entries that has the first two numbers "50" in every row..


The Red Markings can be replaced, the rest should stay.. it is needed due different criteria.

please help.

It should be like this.

=SUMIFS(LEFT(Sheet2!$D$2847:$D$3065;2);Sheet2!$I$2847:$I$3065;$B$6;Sheet2!$G$2847:$G$3065;$C$6;Sheet2!$B$2847:$B$3065;A14)

but it doesnt seem to be working.. where is the error if i may ask..

thanks

regards

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Thread moved to Excel Worksheet Functions forum

I would advise you use Sumproduct (backwards compatible unlike COUNTIFS/SUMIFS/AVERAGEIFS etc... also manipulating datasets incredibly difficult nigh on impossible with aforementioned functions)

Please Login or Register  to view this content.

--: double unary operator

applying a minus will coerce the boolean value (True/False) to an integer ... however using a single minus will obviously coerce True to -1 rather than 1... using a double unary means you - the -1 so reverts to correct Sign --1 = 1

in sumproduct often you see approach

(range=criteria)*(range=critera)

in this case the * does the coercion of the booleans but it's argued that double unary (--) is slightly quicker method (others argue using - is quicker still but this requires an additional test on the result based on count of tests so -- is regarded as safer)