• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula required to SUM numbers only not Dates

Khalid NGO

Khalid NGO

Excel Ninja
Hello everyone,
Greetings...

Once again need your assistance,
I have a sheet like this:

Sum with dates.PNG

I need to sum numbers only (not dates which are mentioned in brackets)

Although I can manually do =C4+C6+C8+C10 …..
And put a ' before each date

But this is not a good way to maintain a sheet with more than 200 rows.

So, how to sum numbers only, without summing the dates (as each date has its number)
Sample file also attached.

Thank you.
 

Attachments

  • Sum with dates.xlsx
    10.5 KB · Views: 25
NARAYANK991 said:
Hi Khalid ,

Another option :

=SUM(N(OFFSET(C$4,{0;2;4;6},)))

Enter this as an array formula , using CTRL SHIFT ENTER.

Copy this across for results in columns D , E , F ,...

Narayan
Click to expand...

Excellent Sir Narayan,

I didn't understood {0;2;4;6} for rows.
Can you clarify how its working?
 
Hi Khalid ,

This is an array of numbers , which is used as the second parameter of the OFFSET function i.e. the rowoffset parameter.

Hence , what is being passed to the SUM function is the cell values from :

C4 , C6 , C8 and C10.

Since the OFFSET function does not accept this and return valid numeric outputs , we wrap it inside the N(...) function , which miraculously converts the error values to valid numeric values.

Of course , if you want to extend this to 200 rows , you cannot use this construct ; the formula will become more complicated , since you will have to generate the array of numbers from {0;2;4;6;...;198;200} using a different method.

Narayan
 
Hi Sir @NARAYANK991,

Your clarification did great help to understand.
You are awesome.

Yes you are right i will not go with complications like {0;2;4;6;...;198;200}
It is good for me to find a better way for dates.

Many thanks for your help.
 
Hi Sir SM,

Superb work...
You make me stunned, glad to see the way you used for SUM and SUMPRODUCT.

Many thanks for your kind help.
 
Haseeb A said:
Hell Khalid,

I know you have a solution. But just a thought. If you have an amount in month, you will also have Total in column J. So you can simply use a SUMIF to check, if column J not blank, sum

=SUMIF($I4:$I11,"<>",C4:C11)
Click to expand...

Wow...
Glad to see the shortest solution.

Thank you Haseeb.
 
Hi Haseeb!

I understand from the formula that ,"<>" means a "isblank() false", but how does it work? Is it just a shortcut of isblank() = false or does "<" and ">" have different logic.

I have used criteria <, > separately but not like this !

Anways thank you for the formula which beats array formula ;)

Regards,
Prasad DN.
 
I sometimes do the same. Often a particular construction will look for all the world as if it must require CSE, but in fact doesn't!

N is quite a useful little function. :) Not only does it here act as a dereferencer, but it also lends the necessary array coercion to the construction.

Cheers
 
You must log in or register to reply here.
Back
Top