For anyone who comes across this post I have found the answer out. It simply requires the most up to date version of the service pack for office. I installed Microsoft Office service pack 3 and this problem was solved. I would like to thank hui for looking into this for me and anyone else who...
Ok I see the problem now. What you need to understand about COUNTIF is that it uses an exact search function. You cant search in a cell containing 1234--- asking for 1 because its looking for an exact match. 1 is not equal to 12345-- therefore the value is false and it returns a 0. You can...
Why thank you sir.
Firstly I am opening the document, this version is stripped down and only contains the front page and data. I am going to the bottom left hand chart, right clicking it and going to 'Select Data' Once there I chose the first series which is electricity spend and go to edit...
As far as I know you cannot use SQL language in excel, maybe you could use a text string though to write SQL syntax? For Example:
=IF(A1 = "Yes", "SELECT * FROM Table_Name WHERE Criteria >= 100", "")
Then the Cell will only display /SELECT * FROM Table_Name WHERE Criteria >= 100/...
That formula is fine and should work, have you got formulas on to self calculate? Check the bottom left hand corner does it say calculate? if so you need to enable self calculation in your settings. The syntax for count if is as you put it
=COUNTIF(Range,Criteria)
The only other thing I can...
Changing the format wont change a date from 21-08-13 to 7/8/2013 because they are two different dates. However sometimes when data is imported in certain ways formatting appears to be none responsive, however if you select your column then go to data/text to columns then just hit finish that...
Erm, maybe im not reading this right but that is not the syntax of a countifs formula NARAYANK991. A count ifs formula looks like this:
=COUNTIFS(Criteria_Range1, Criteria1, Criteria_Range2, Critera2) etc.
However getting back to the problem at hand, your formula is very simple if im reading...
You can 'order by' on column A and that will bring all of your values to the top and all of your blanks to the bottom. Then you can un-filter to return everything to its previous state. If you don't know how to do that, insert a new a new column in place of A and give it a sequential numbering...
hmm, first I would try inserting a new cell or maybe removing a cell. Then maybe try convert it to an actual Table (Ctrl T anywhere in your table) If its already a table try bring back to a normal range then re convert it again. Otherwise copy the contents of the table out, paste them as values...
Ok here it is:
-Removed Link
The workbook has a few Worksheets but I've cut them out to make it easier. ill explain what to do. On the worksheet called 'Front sheet' there are two charts at the bottom. Edit either one going to 'Select Data' You will notice there are 2 or 3 series depending...
Conditional formatting can only return a 'format' based on a Boolean 'Condition'. That is, a result that has a TRUE/FALSE answer. Hence -condition-al -format-ing. You can use a conditional formula however it will not return you a value i'm afraid. I don't know anything about VBA to tell you if...
I have made a chart and it is fed from a dynamic name range. The named range basically is an offset function that returns a range based on cell references with dates in. But anyway, When I apply it to my chart the series value looks like this:
=Gas_Dashboard!Gas_Spend_Dynamic
Where...
I see I think I get it now, if the first criteria is "ALL" then its true, and the first true false value will affect the second set so in this case all become true. But that is also true for any blank cells also, so this would work only for a table range or a static dataset. The formula...
I get that Luke, thanks for explaining. I understand that the OR formula is saying give me a count of this criteria OR this criteria but the formula I posted is very much different to this for the following reason:
Lets say the otions in the cell reference in the first criteria are: ALL...
Hi there I was wondering if someone could help me understand this formula. I understand what it does and I understand what results it returns, I just dont understand HOW. Any help would be very much appreciated, ill explain what I know underneth:
So as an overview what this formula does is...