tag:blogger.com,1999:blog-1869924468172210809.comments2016-06-24T07:09:16.065+01:00Not Just NumbersGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger1347125tag:blogger.com,1999:blog-1869924468172210809.post-53554537062509379932016-06-18T08:55:22.482+01:002016-06-18T08:55:22.482+01:00Awesome and niceAwesome and niceJeremy R. Gepharthttp://www.blogger.com/profile/14532264814565369687noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-52993159121911480142016-06-16T13:19:47.873+01:002016-06-16T13:19:47.873+01:00I was finding this trick to add $ sign on excel. O...I was finding this trick to add $ sign on excel. Ok i got it today.Dodsonhttp://www.pccenter.onlinenoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-67595139497597931692016-06-09T13:17:24.860+01:002016-06-09T13:17:24.860+01:00niceniceAnnahttp://www.blogger.com/profile/05734220215058808818noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-13114496784543924922016-06-08T10:03:36.582+01:002016-06-08T10:03:36.582+01:00Thanks Glen
You are amazingThanks Glen <br />You are amazingUnknownhttp://www.blogger.com/profile/07990709309156562964noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-17225171147091017452016-06-07T12:46:50.523+01:002016-06-07T12:46:50.523+01:00Thank you sir. With your help finished the job as ...Thank you sir. With your help finished the job as planned.<br /><br />Thank youChengayihttp://www.blogger.com/profile/06854907671854354249noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-91714128512532600722016-06-02T19:22:21.903+01:002016-06-02T19:22:21.903+01:00I'm sure if you post the copies of the formula...I'm sure if you post the copies of the formula you used and the yext you applied it to (both copied from your spreadsheet). I will be able to spot the problem.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-35386827857294184952016-06-02T12:25:40.835+01:002016-06-02T12:25:40.835+01:00It doesnt work ..i have tried both and i am tierd....It doesnt work ..i have tried both and i am tierd. Prerana Priyambadahttp://www.blogger.com/profile/05129067957104283031noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-59285703708501376972016-06-02T09:04:52.041+01:002016-06-02T09:04:52.041+01:00niceniceAnnahttp://www.blogger.com/profile/05734220215058808818noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-2555398107131331802016-06-02T07:31:45.319+01:002016-06-02T07:31:45.319+01:00thanks for the info..was helpful
thanks for the info..was helpful<br />PRASOON KUMAR Phttp://www.blogger.com/profile/08767105339210970645noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-682789658353083992016-05-18T18:54:37.329+01:002016-05-18T18:54:37.329+01:00I was looking for a solution to this problem, howe...I was looking for a solution to this problem, however, my sheets are all date named... like 21st March so the formula was not working very well.<br /><br />The best way to deal with that is to create a 'bookstart' and a 'bookend' sheet either side of all your sheets then hide those sheets so they do not bother you. <br /><br />Now my formula bookstart:bookend z11 works wonders and also auto adds all new sheets.Leon Alexanderhttp://www.blogger.com/profile/16630121547612808608noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-34565744929634872032016-05-10T18:59:11.675+01:002016-05-10T18:59:11.675+01:00Thanks Paul. I probably should have covered wildca...Thanks Paul. I probably should have covered wildcards!Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-51877300990514267782016-05-10T18:58:29.273+01:002016-05-10T18:58:29.273+01:00Me too Jim! I nearly mentioned both PivotTables an...Me too Jim! I nearly mentioned both PivotTables and helper columns but thought the post was already quite long and wanted to stick to the priority of explaining SUMIFS.<br /><br />I tend to go to PivotTables as a first port of call for this kind of thing, but they are not appropriate in every situation - and this post was about SUMIFS.<br /><br />In practice, I would typically use a helper column for the month, i.e. =MONTH(A2), but I thought it would be better to explain how to use < and > with SUMIFS.<br /><br />Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-44587740270896281532016-05-10T12:16:37.151+01:002016-05-10T12:16:37.151+01:00yes, that works
still learning after all these yea...yes, that works<br />still learning after all these years<br /><br />last para still applies (even in a simple example)<br /><br />jimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-23106127379120069362016-05-10T12:06:00.027+01:002016-05-10T12:06:00.027+01:00Or use wildcards in the criteria, so in Jim's ...Or use wildcards in the criteria, so in Jim's example you could write<br /><br />=SUMIFS(D2:D21,C2:C21,"M*")Paulnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-61627008262014474532016-05-10T10:36:20.306+01:002016-05-10T10:36:20.306+01:00If, as a convoluted example, you want all the sale...If, as a convoluted example, you want all the sales for Salespersons beginning with M, putting a LEFT function in will not work (or any function in the criteria range)<br />For that, you need to add a helper column or use SUMPRODUCT to produce a nightmare like this:<br /> =SUMPRODUCT($D$2:$D$21*(LEFT($C$2:$C$21)="M"))<br /><br />A much better solution for nearly all these situations is to use a Pivot Table (even better if you format your data as a Table first - which may even do the job by itself)<br /><br />jimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-9970076784641586242016-04-28T20:00:10.578+01:002016-04-28T20:00:10.578+01:00Rather than use the wildcard, have a dummy workshe...Rather than use the wildcard, have a dummy worksheet at the end included in the sum, then just inswrt the new worksheets before that one.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-65613351676096297082016-04-28T19:59:47.692+01:002016-04-28T19:59:47.692+01:00Rather than use the wildcard, have a dummy workshe...Rather than use the wildcard, have a dummy worksheet at the end included in the sum, then just inswrt the new worksheets before that one.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-21394010730592263872016-04-28T18:09:41.363+01:002016-04-28T18:09:41.363+01:00It is true about: =SUM('work*'!A1), but if...It is true about: =SUM('work*'!A1), but if you have let's say work1 and work2, excel populates the formula with the actual sheet names. For example, when you put in the formula box: =SUM('work*'!A1), after the enter you get: =SUM('work1:work2'!A1). Is there any way to keep the original formula, so it can be still valid when adding more worksheet with the same pattern name)? Thanks in advance. DavidDavid Lealhttp://www.blogger.com/profile/15282075893436086442noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-12665074871954824072016-04-25T12:37:32.532+01:002016-04-25T12:37:32.532+01:00Which cells have you highlighted to apply the form...Which cells have you highlighted to apply the formula to?Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-49175842740214663492016-04-25T12:03:23.780+01:002016-04-25T12:03:23.780+01:00Hi great post.
I have a table that has standard co...Hi great post.<br />I have a table that has standard conditional formatting showing green if the figure in the cell is positive, and red if negative. I have another table underneath with the same formatting (1 table shows sales, the other shows profits). I now want to format the next column to green if both sales and profit are green/positive, and red if both are red/negative, and yellow if sales are positive and profits are negative. I have tried to insert a formula in conditional formatting option e.g. for green:<br />=and(a2>0,a29>0)<br />but its not giving me the right results. Can you please help me? Where am I going wrong? Thanks a lotFraz Rnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-59602006842141027462016-04-24T09:07:31.143+01:002016-04-24T09:07:31.143+01:00Your formula should return bkank if A1 is actually...Your formula should return bkank if A1 is actually empty, however if A1 contains a space for example, it will return "true".<br /><br />The following should do what you want even if there is text (such as a space in A1:<br /><br />=IFERROR(IF(VALUE(A1)>0,"true",""),"")<br /><br />VALUE(A1) will return the value of A1, or an error if A1 does not represent a number.<br />Encasing the whole function within the IFERROR function will return the second argument of the IFERROR function if the first one returns an error.<br /><br />=IFERROR(YourFunction,"")<br /><br />Will return "" if YourFunction returns an error, otherwise it will return the result of YourFunction.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-23992403753252750292016-04-24T06:45:14.119+01:002016-04-24T06:45:14.119+01:00I'm using this statement =IF(A1>0,"tru...I'm using this statement =IF(A1>0,"true",""). The problem is that if A1 is blank(which isn't greater than 1), it is still putting "true" in B1. How can I make it show cell B1 as blank if A1 is blank?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-49795998362528406552016-04-12T21:44:54.461+01:002016-04-12T21:44:54.461+01:00Thanks for asking the question, and thanks for the...Thanks for asking the question, and thanks for the answer, Glen.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-6121238306387107642016-04-10T07:11:02.139+01:002016-04-10T07:11:02.139+01:00If the current sheet is within the range Worksheet...If the current sheet is within the range Worksheet2, say, in our example, then it should work no differently. If not, just add it as below:<br /><br />=SUM('Worksheet1:Worksheet5'!A1)+A1Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-25316757042401269642016-04-09T19:22:20.978+01:002016-04-09T19:22:20.978+01:00How can we also add the current sheet as well? How can we also add the current sheet as well? antepartshttp://www.blogger.com/profile/03454193715204894783noreply@blogger.com