## Hot answers tagged worksheet-function

8

Data validation will not change formatting of the cells. You will need to use Conditional Formatting to do what you desire.
Once you have your lists in place, apply conditional formatting rules to those cells which use a formula that checks for the words RESTRICT and CASCADE.
Select the cells with the lists
Create a new conditional formatting rule
Choose ...

5

In B1 enter 1
In B2 enter:
=COUNTIF($A$1:A2,A2)
and copy down.
For example:

4

Input the following array formula (Ctrl+Shift+Enter) into cell C3 and then drag it down. I extended your scenarios just a bit and it performs as expected.
=SUM(IF(LEFT(B3,LEN($A$3:$A$7))=$A$3:$A$7,1,0))

2

SUMIF function does a conditional sum based on values in another range, e.g. for your example you can use this formula in E4
=SUMIF(A1:A3,"X",E1:E3)

2

I'm not sure I understand exactly the effect you are after, but if you want to be able to copy (or drag to extend) a formula while keeping some reference the same, you can prefix either the column or row (or both) identifier with a $ sign.
For example, if you have in cell A1 =B3 and copy this to cell A2, it becomes =B4 because the reference is updated to ...

2

Your question isn't clear, but this should get you going
Sub AvoidThePirates()
Dim startRow As Integer
startRow = 2 ' EDIT THIS ME HEARTIES
Dim columnToLookUp As String
columnToLookUp = "C" ' Arrrgh ye scurvy sea dog
Dim columnResults As String
columnResults = "D" ' Show me ya gold
'Touch below this and I'll feed ya to the sharks
Dim isUpper As ...

1

After adding the new column, it appears that First Name is in B1. In A2, you could add any one of the following formulas,
=COUNTIFS(B:B, B2, C:C, C2)
That will produce the number of occurrences for the first and last names (e.g. 1 for single, 2 or more for duplicates).
If you want a more complicated formula with simpler results then perhaps,
...

1

You need to provide an extra level of processing so each row in F3:F11/G3:G11/H3:H11 is added. This can be done with a SUMPRODUCT wrapper on a SUMIF.
The formula in F13 is,
=SUMPRODUCT(SUMIF($B$3:$B$14,F$3:F$11,$C$3:$C$14))
Fill right as necessary.

1

Could it be as simple as
D2 = 1
D3 = =IF(A2<>A3, 1, IF(C2=C3, D2, D2+1))
(and drag down)?

1

Earliest due date (column C, starting at C2) can be calculated with =MIN(A2:B2), then copy this formula down across the column.
You can then get the matching headers into column D, starting at D2, with =IF(A2<=B2,$A$1,$B$1). Then, again, copy this formula down the entire column.
In case both overhaul dates are identical, this solution will always choose ...

1

Ok, found it.
Assuming the values are in a table, and its column is named "Column1":
=REPLACE([@Column1],1,FIND(LEFT(SUBSTITUTE([@Column1],"0","")),[@Column1])-1,"")
This will produce the desired output as shown in the question.
The core of the solution is handled by this formula, which counts the 0's before the text value we're looking for.
...

1

Another approach would be to convert to decimal then back again. The HEX2DEC worksheet function will only accept 10 digits/characters as input but RIGHT will take care of that. Instantly converting back with DEC2HEX (while leaving the optional [places] parameter out of the equation) should result in a trimmed HEX value.
The formula in B1 is,
...

1

You could try applying the concatenate function before using conditional formatting.
http://blog.contextures.com/archives/2013/04/11/highlight-duplicate-records-in-an-excel-list/

1

Go back to Conditional Formatting and delete any prior rules you have created for this purpose, then (full columns A:H selected) create a new rule using the option to Use a formula to determine which cells to format. Supply the following to the Format values where this formula is true textbox:
...

1

The AVERAGEIFS criteria come through as AND conditions. You require an OR condition since any one row could not be both 1 and 2.
In the above, the sum of the blue/greens is 23, their count is 5 making the average 4.6. The formula in E2 is either,
=SUMPRODUCT((C2:C8)*(B2:B8=F1:G1))/SUMPRODUCT(--(B2:B8=F1:G1))
... or with the colors hardcoded as,
...

1

The only way you can keep the formula simple is if you can come up with criteria that are not mutually exclusive. For example,
=AVERAGEIFS(Pupil_Score,Race,">=2",Race,"<=3")
This works only if race numbers are adjacent.
Others have suggested array (CSE) formulas so I won't repeat that. Those formulas tend to be rather complex. If you want to keep ...

1

You can use an AVERAGE array formula, e.g.
=AVERAGE(IF((Race=1)+(Race=2),Pupil_Score))
confirmed with CTRL+SHIFT+ENTER
The "+" effectively gives you an "OR"
You can easily add extra conditions by adding IF functions, e.g. assuming "range1" needs to be "x"
=AVERAGE(IF((Race=1)+(Race=2),IF(range1="x",Pupil_Score)))
See example here

1

Awww, figured it out. I had to put in a nested IF to check if the month = 6 and date is >= 20.
=IF(AND(MONTH(P2)=6,DAY(P2) >= 20),YEAR(P2)+1,IF(MONTH(P2)>=7,YEAR(P2)+1,YEAR(P2)))
I don't like to use nested IF statements, but it seems like a necessary evil this time.

1

Enter a 3-dimensional formula. This will evaluate every worksheet between (and including) the start (Sheet1) and end (Sheet3) worksheets you're interested in.
=COUNTA(Sheet1:Sheet3!A1:A99)

1

You need to define each range for each sheet e.g.
COUNTIF(Sheet1!C2:C150,"*string*")+COUNTIF(Sheet2!C2:C150,"*string*") ...
If you want to be clever, you can say put each sheet in the column A, and in column B use
=COUNTIF(INDIRECT("Sheet"&A1&"!A1:A5"),"*string*")
Or whatever fits your naming scheme. Then drag it down for each count and sum ...

1

Here is the equivalent VBA code to count the number of cells containing at least one string
Sub StringCounter()
Dim r As Range, s As String, i As Long
s = "*string*"
For Each sh In Sheets
Set r = sh.Range("C2:C150")
i = i + Application.WorksheetFunction.CountIf(r, s)
Next sh
MsgBox i
End Sub
EDIT#1:
Here is the code to ...

1

The part you queried is doing as you say:
$B$1:$B$15<>""
That returns an array of TRUE/FALSE values, FALSE if each cell is blank and TRUE if it's not. In the formula an array of the relative row numbers is divided by that array - when you divide by TRUE that acts as a 1 so you just get the row number, when you divide by FALSE that acts like zero so ...

1

Hmmm. Strange and not as specified by Microsoft. I tried to reproduce the problem, but on Excel 2011 for Mac as well as on Excel 2010 for Windows it works as expected using the following formula: =COUNTIFS(A1:A9;E1;B1:B9;">="&E2;B1:B9;"<="&E3) (I used three cells for the lookup values you want to use).

1

Select the cells in column A. Create a new conditional format based on a formula (Click Conditional Formattting > New Rule > Use a formula to determine which cells to format).
Use this formula
=COUNTIF($B:$B,A1)
Click the Format button and select a format for example a fill color. Click OK.

1

Use "Remove Duplicates" option from Data tab. Select the column 'A' > go to Data tab > click on "Remove Duplicates". This will remove all the duplicated values in the column.
Now, use 'Summation' formula to sum the rest of the cell values in the 'A' column
Hope this helps.

1

As far as I know, there is no way to concatenate arrays without VBA. The concatenate function accepts only single values. For example, you can't do this:
=CONCATENATE(A1:A3)
So the problem is not the use of arrays, but the design of the built-in concatenate function.
From what I've heard, there are add-ins that can do this. MOREFUNC is one, it has a ...

Only top voted, non community-wiki answers of a minimum length are eligible