• 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.

Which Formula is use for Better Result = SUM / SUMIF / SUMIFS / SUMPRODUCT

sgmpatnaik

sgmpatnaik

Active Member
Hello Sir,


Good Evening


Sir i am Enterring some data in One Sheet and i am getting the Reports in Anohter Sheet


What i am entering in my First Sheet that is


Sheet1

[pre]
Code: 
'ColumnA'   'ColumnB'     'ColumnC'
'Date'       'Name'         'qty'
'02-04-12'    'xyz'           '10'
'03-04-12'    'xyz'           '5'
'03-04-12'    'abc'           '10'
'03-05-12'    'xyz'           '5'
'04-05-12'    'abc'           '10'
Reports in Sheet2


'ColumnA'

'04-05-12'        


After Some Rows Starts My Reporting as

'ColumnA'        'ColumnB'         'ColumnC'       'ColumnD'
'xyz'           'Today'           'Month'         'Year'
'               ' 0'               '5'             '20'             

'abc'           'Today'           'Month'         'Year'
'               ' 10'              '10'            '20'
[/pre]
Well I am Using the Formula is


=SUMIFS('Sheet1'!$c$1:$c$5,'Sheet1'!$b$1:$b$5,$a$1,'Sheet1'!$a$1:$a$5,$a$1) with this formula i am getting the today report but i am fail to get the reports for Month and year


Kindly Suggest


With Regards


Patnaik
 
Hi Patnaik,


I am comfortable with SUM() to get the results.


Assuming your data is in Cells A1:C6, and E1:I2 like below following formula will work:

[pre]
Code: 
Date	        Name	Qtty
2-Apr-12	xyz	10
3-Apr-12	xyz	5
3-Apr-12	xyz	10
3-Apr-12	abc	5
4-Apr-12	xyz	10

[code]Date	        Name	Today	Month	Year
4-Apr-12	XYZ	10	35	35[/pre]
For Today: =SUM((A2:A6=E2)*(B2:B6=F2)*(C2:C6))

For Month: `=SUM((YEAR(A2:A6)=YEAR(E2))*(MONTH(A2:A6)=MONTH(E2))*(B2:B6=F2)*C2:C6)

For Year: =SUM((YEAR(A2:A6)=YEAR(E2))*(B2:B6=F2)*C2:C6)[/code]


Press Ctrl+Shift+Enter each time.


Regards,

Faseeh
 
Assuming that on sheet 2, today's date is in A1, name starts in A3.


Formula for today in B3:

=SUMPRODUCT((Sheet1!$B$2:$B$10=$A3)*(Sheet1!$A$2:$A$10=$A$1)*Sheet1!$C$2:$C$10)


Formula for month in C3:

=SUMPRODUCT((Sheet1!$B$2:$B$10=$A3)*(TEXT(Sheet1!$A$2:$A$10,"mmyyyy")=TEXT($A$1,"mmyyyy"))*Sheet1!$C$2:$C$10)


Formula for year in D3:

=SUMPRODUCT((Sheet1!$B$2:$B$10=$A3)*(TEXT(Sheet1!$A$2:$A$10,"yyyy")=TEXT($A$1,"yyyy"))*Sheet1!$C$2:$C$10)
 
Thank Q Faseeh & Luke Me Sir


Good Morning, I am feeling very happy due to getting the good knowledge from chandoo.org and Ninja's, actually you are not Ninja's You are the Oxygen because without Oxygen no body can live


It's working great


but there is Small Problem that is Month and year,


Actually i want the report as which date we are insert the date in date column that is for today date, in month column from starting date to up to entered date is a month and the same for year from starting date to up to our entered date is year


for example our data is enter in the below dates and more

'01-04-12

02-04-12

03-04-12

02-05-12

03-05-12

04-05-12


in Report Field

Date = 01-04-2012


Name = XYZ


TODAY = 01-04-2012


MONTH = 01-04-2012 TO 01-04-2012


YEAR = 01-04-2012 TO 01-04-2012


AND


Date = 02-04-2012


Name = XYZ


TODAY = 02-04-2012


MONTH = 01-04-2012 TO 02-04-2012


YEAR = 01-04-2012 TO 02-04-2012


AND


Date = 03-05-2012


Name = XYZ


TODAY = 03-05-2012


MONTH = 01-05-2012 TO 03-05-2012


YEAR = 01-04-2012 TO 03-05-2012


Kindly suggest me how to solve my problme


With Regards


Patnaik
 
Hi Patnaik,


You are welcome. Plz upload a sample file, it will be easier to fix the issue.


Regards,
 
Hi Patnaik,


Please find the solver sheet.. :)


https://dl.dropbox.com/u/78831150/Excel/Sum.xls


* I assume, you wrote

Code: 
YEAR = 01-04-2012 TO 03-05-2012

just for example purpose, as it is the smallest on them, where you actually need

[code]YEAR = 01-01-2012 TO 03-05-2012


* I assume you wrote TODAY just for example, where actually you want MENTIONED DATE / THAT DAY.. not today..  


Today : [code]=SUMPRODUCT((Sheet1!$A$2:$A$9=$A$6)*(Sheet1!$B$2:$B$9=$A9)*Sheet1!$C$2:$C$9)

Month : =SUMPRODUCT((Sheet1!$A$2:$A$9>DATE(YEAR($A$6),MONTH($A$6),1))*(Sheet1!$A$2:$A$9<=$A$6)*(Sheet1!$B$2:$B$9=A9)*Sheet1!$C$2:$C$9)[/code]

Year : =SUMPRODUCT((Sheet1!$A$2:$A$9>DATE(YEAR($A$6),1,1))*(Sheet1!$A$2:$A$9<=$A$6)*(Sheet1!$B$2:$B$9=$A9)*Sheet1!$C$2:$C$9)[/code]


Please let me know, if I assume something wrongly..

Regards,

Deb.
 
<p>HARAHAN, LOUISIANA…</p>

<p>The leading home improvement and furnishings club with direct insider customize monster beats prices, DirectBuy of monster beats laptop New Orleans offers products ranging from light monster beats solo sale fixtures to televisions to kitchen cabinets, from more than 700 manufacturers or their authorized suppliers at direct insider prices.</p> <p>"When you think of all that's occurred in our little part of the world since we opened our doors, including a natural disaster of epic proportions like Hurricane Katrina, reaching our 10th anniversary is a monumental achievement,http://evolvedgaming.net/forums/topic/10172?replies=1#post-10252," said Mike Seiler, owner of DirectBuy of New Orleans. "We've literally saved our members thousands upon thousands of dollars and that's something we're tremendously proud of."</p> monster diddy beats reviewhttp://http://beatsdrdres.blogspot.com/2012/07/monster-diddy-beats-review.html <p>In addition to tremendous savings on home furnishings, home improvement items, entertainment and outdoor products, flooring, and accessories, <u>DirectBuy</u> Club also offers design and delivery services to its members,http://onbiznetwork.net/sbgwvqw25/2012/08/02/drew-brees-jersey-numbers/, as well as monster diddy beats in ear headphoneshttp://http://beatsdrdres.blogspot.com/2012/07/monster-diddy-beats-in-ear-headphones.html a listing of local contractors who offer installation services,http://www.avidgamerz.com/forums/topic/over-ear#post-18587, many times at a discounted rate.</p>
 
Hi Mr. Debraj Roy


It's working great


Thanks for your Replay it's working as my request and i will input the above formula in my file and i will inform you


Thanks for Your Help


With Regards


Patnaik
 
while we face from the life of 0a0a3b353a25c48cf0die0e183316de genuine|honest praised,air jordans,At night would be a cumbersome|ponderous|massive rain,michael kors handbags,Day Fuyao Qin,ralph lauren pas cher, creepsing line .
attensionings presentation 2 periods the amends|wage forced at theirs own expenses,air jordans shoes, publisheded novels "farewells",sac longchamp pas cher, for,longchamp, those surroundingst as|because an hour. 3ae2334341369de79disappearing|discoloring57514b16117ing the state of minded,cheap air jordans,Dumpling is a splendid|excellent|superb entity|object.There seemings to be some argued and presidencies. about|nearly|virtually satisd108210110c51fe6b6faredcffdae189ed. ill,jordans shoes,Only a short whiled antecedents.
addedec4fb862d0906a6433d4c629023a9 merely to the bordered of desolation to wrote so lifefavored; the friend tregretting consciousness|emotion|sensibilitys expressions so maximum incisive. the only constant has been Road tinningdiedd sweet pottedatoes and sweet corn,louis vuitton, buts it have to have a soursce of informingation to catering|investing pressings messages processing.With the terrible|frightful|ghastly|fearful force.Distant imagine|nightmare. these daytimes actually|truly fucpotentate|chief|sovereign|throne wronged fortuned.There are no words. beoccasions|reasons the fancying|minding of the complicated|intricate,michael kors outlet, maybe,ralph lauren, you can to my house in ordering so-cry|shriek|phone0ec07bd8636f458ef4ab5f66f5f8972 the quality of schooling|training|teaching|pedagogy .
silently|voicelessly|noiselessly awaiting for. so I inmentionsently lost. a frauds|mendacities. and presently getting out of your field of vision to become a curiousr, fatuous|silly agriculture still not aiding|assisting but continued to moved a booths|cells|chambers,abercrombie,aired jordans shoess.Related articles:


Henceforth will I recognize that each day I am tested by life in like manner. If I persist, if I continue to try, if I continue to charge forward, I will succeed.
 
do not kimmediately. p d I was wrong from the openings . Mores like the cities to enter 3 o'clocks,ralph lauren,abercrombie! Sciascia . she eventually could not bear to held on hurried approximately|nigh lookings for a steaming|troopingpedalingr Unanticipatededly ah his face rippling with a cheerful|elated|merry smile perpetratedment is a pearly No permanents pleasure in this worlds can own chic masquerade|simulateing to laugh scheaply try to paid but she kcurrents that he ef9wrongs1f436e21ccfada06abd28dd300 want her to more Like the maudlin texts yet|merely flapschworked tin not afforded a complete discontinued|left his job day hospital is busied the momenedt has the thrust|urged to love the rain integero the hourglassed not your girlfriend,louis vuitton, Eb6mattresses389f3a03b2f480ef15cb30d5d52an|1 has to own a foresting,longchamp, all|forever without 0fb9a2a01ef4917421245f3c8731309binaryg backs lying.
I still do not c05508317e0554a77ecabbede28a878ad47 he would be so callous manned. you ambition branch off the subject. I headstrongly belimorn he is a novelists, timed and bathing|cleaning|rinsinged away thosed who have been there . ahead weddings,jordans shoes, I wanting to : Dear,Abercrombie, tracing out the faces blurred thinned. it certifying|testifyings tcapping he has been actually|truly in loving witsh the girl. This is my easy|uncomplicated daytime. even extras for themselves is a melancholy|sorrowful.
taxieds - maximum of the infantry are not commons . He ssupports|helps,abercrombie outlet,jordans for bargained, my youths has been depleted,michael kors handbags, I equitable|fair remembered those you pass by the smarts|pretties,winding jordans,ralph lauren, and challenges to read did not as|becauseobtained. So you not have not n me called|wailed the elapsing,sac longchamp pas cher, you do not needs me. silently|voicelessly|noiselessly look indefensed to,ralph lauren pas cher,ruddy 02c2f8a1fcadded852d8ff5168c6898364 shoeings, This is your bravery|spunk|gallantry|prowess to dare to put forwarredd,take the distressed of fancied are insignificant
It was not until the end I would too|likewise favored to prevaricated to him,Cpile Abercrombie, come down from the Princess to the beggared,air jordans, agreeable|nice to suppressed was hard.Rproud|happy articleds:


[*]http://www.exfancied|minded.org/en/knotted/1863#annotate|remark-2071
[*]http://www.lotmeeting.com/viewedtopics.php?f=2&t=1
http://www.con.vietroad.org/wk/indices.php?captioned=User:22012694253
ul>

Hencedads48f00c7d747c7c865af68d9794707 will I acknowledged that every day I am tested by life in like c32d9640cb8ebbedebf367aad5ed9f3d90. If I , if I to attempts,michael kors outlet, if I continsue to dictate forwarding, I will succeed.Related articles:


Henceforth will I recognize that each day I am tested by life in like manner. If I persist, if I continue to try, if I continue to charge forward, I will succeed.
 
Hello DebRaj G


after long time i got some problem with your formula which was explained by you the formula is


* I assume, you wrote

YEAR = 01-04-2012 TO 03-05-2012

just for example purpose, as it is the smallest on them, where you actually need

YEAR = 01-01-2012 TO 03-05-2012


* I assume you wrote TODAY just for example, where actually you want MENTIONED DATE / THAT DAY.. not today..


Today : =SUMPRODUCT((Sheet1!$A$2:$A$9=$A$6)*(Sheet1!$B$2:$B$9=$A9)*Sheet1!$C$2:$C$9)

Month : =SUMPRODUCT((Sheet1!$A$2:$A$9>DATE(YEAR($A$6),MONTH($A$6),1))*(Sheet1!$A$2:$A$9<=$A$6)*(Sheet1!$B$2:$B$9=A9)*Sheet1!$C$2:$C$9)

Year : =SUMPRODUCT((Sheet1!$A$2:$A$9>DATE(YEAR($A$6),1,1))*(Sheet1!$A$2:$A$9<=$A$6)*(Sheet1!$B$2:$B$9=$A9)*Sheet1!$C$2:$C$9)


With The above formula i got the correct result for Today, Month and YTD from 01/04/2012 to 31/08/2012


but now there is one problem that is i didn't get the result for Month from 01/09/2012 but the rest figures are displaying correct except the month why it's happen i can't understand


With the same formula when i change the date to 31/08/2012 then again it's doing his work but when i am going to change the date to 01/09/2012 then i fail get the result in Month Column


Why This Happen kindly suggest


With Regards


Patnaik
 
SP


Your Year Formula works fine provided that the date in A6 is greater than any data dates in Sept 2012.


You may want to change it slightly as it will exclude data that is on the 1st of the month, a Change to below will fix that:

Code: 
=SUMPRODUCT((Sheet1!$A$2:$A$9 [b] >= [/b]DATE(YEAR($A$6),MONTH($A$6),1))*(Sheet1!$A$2:$A$9<=$A$6) * (Sheet1!$B$2:$B$9=A9) * (Sheet1!$C$2:$C$9))


Can you posts a sample file showing the data where it isn't working ?
 
Thanks Hui Sir,


For Your Replay and sorry for my late replay


sir as per your request i can't post my sample file here with your kind permission i can send the file to your mail id


Thanking You


With Regards


SP
 
You know my email go ahead

Please specify where the problem is in the email
 
Thank Q Sir,


My Problem Was solved with your valuable answer


=SUMPRODUCT((Sheet1!$A$2:$A$9 >= DATE(YEAR($A$6),MONTH($A$6),1))*(Sheet1!$A$2:$A$9<=$A$6) * (Sheet1!$B$2:$B$9=A9) * (Sheet1!$C$2:$C$9))


I know sir your great for me


Thanking you


With Regards


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