Solved

# Conditional sum in excel 2010

Posted on 2012-08-14
724 Views
Dear Sir
im trying to sum values depending on Conditional  vlaue

code  code2     date      amount
C      N2      11/01/2012      4860
C      N1      29/01/2012      4850
C      N4      20/02/2012      5010
C      01      01/03/2012      1210
C      01      01/03/2012      1210
D      F6      11/01/2012      -20
D      01      14/01/2012      -1210
D      01      14/01/2012      -1210
D      01      14/01/2012      -1210
D      01      14/01/2012      -1210
D      01      29/01/2012      -1210
D      01      29/01/2012      -1210
D      01      29/01/2012      -1210

wht im trying to do is to sum only vlues that in the column amount that dosen't have alphabetical character in column code2
0
Question by:osama120

LVL 50

Assisted Solution

Try this

=SUMPRODUCT(ISNUMBER(B2:B100+0)+0,D2:D100)

where B2:B100 contains codes and D2:D100 the amounts, adjust ranges as required

regards, barry
0

LVL 8

Expert Comment

Barry is the master of formulas. :)

I was just wondering what's the purpose of +0 at the end of the range?
0

LVL 50

Accepted Solution

Thanks, techfanatic

The first +0 is to deal with text formatted numbers, e.g. if B2 contains 01 formatted as text then ISNUMBER(B2) returns FALSE but I think 01 should be recognised as a number here, even if text formatted, so ISNUMBER(B2+0) should return TRUE as required

regards, barry
0

LVL 8

Expert Comment

Thanks for explaining Barry.
0

Author Closing Comment

thanks
0

## Featured Post

### Suggested Solutions

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.