## Hot answers tagged worksheet-function

4

Rather than a SUMIF with one criteria, use SUMIFS so you can specify two criteria.
So you simply need to sum everything in column H where the value in column A is equal to, or after 1st September, AND before 1st October.
=SUMIFS(H:H,A:A,">=01/09/2014",A:A,"<01/10/2014")

3

=SUM(SUMIF(A1:A4,{"<0",">0"}))
If all your values are non-negative then simply:
=SUMIF(A1:A4,">0")
Regards

3

Sure, you can use this formula and drag it down -
=INDIRECT(CONCATENATE("Sheet",ROW(),"!$A$1"))
It will use the row where it's at to determine the sheet number.
You could also use something like this
=INDIRECT("Sheet"&ROW()&"!$A$1")
these both use the INDIRECT() function, which is volatile.

2

Using INDEX works too.
=SUM(INDEX(A:A,B1):INDEX(A:A,B2))
Referencing an earlier answer which suggests using this method instead of INDIRECT.

2

Sounds like a job for pivot table!
Select the range and go to Insert - Pivot Table
For row labels, have whatever your rows are.
In the values field place your numbers, click the drop down and go to value field settings.
Now show value as and select % of total in the drop-down.
Now they are summarized by percentage of total!
click for full size image

2

At first find lower and upper bounds of intersection
The Lower bound of intersection (IL) is highest of min([a;b]) and min([c;d]);
The Upper bound of intersection (IU) is lowest of max([a;b]) and max([c;d]);
Then count days in range [IL;IU], assuming that it's zero if IL>IU
IL=max( min(a,b) , min(c,d) )
IU=min( max(a,b) , max(c,d) )
DAYSCOUNT= min( 0, ...

2

Step 1: Under the "Data" tab of excel - under Data tools group look for "Text to columns" icon.
Step-2: Select the text string you have to convert to columns
Step-3: click on Text to columns - in the dialog that opens select - Delimited option - Click "Next"
Step-4: In the succeeding dialog - Select ";" as the Delimiter. Look at the preview and if ...

2

If your temperatures are in column A, you can put this in cell B1 and drag it down
=(A1-MIN(A:A))/(MAX(A:A)-MIN(A:A))
Change the format of those cells to percentage and baby, you got stew going!

2

Another option is to use SUMPRODUCT and use MONTH and YEAR to establish conditions for inclusion in the sum.
=SUMPRODUCT((MONTH($A$1:$A$10)=9)*(YEAR($A$1:$A$10)=2014),$H$1:$H$10)
This checks for the month being September and the year being 2014.

2

There IS a simple way of doing this.
Say you have 50 in the cell in question, and want to add 30 to it.
Type 30 in a random cell somewhere else.
Copy (Ctrl-C) that cell
Then the clever part:
Right-click on the cell with 50 in it, and select Paste Special.
In the box that comes up, select 'Add'.
This will add the value directly, without you having to do ...

2

Please give this a try. In Sheet1 B1, please type the following formula and see:
=VLOOKUP(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&WEIBULL(3,5,7,))-1)),Sheet2!$A$1:$B$4,2,0)
Hope it helps

2

Sure use this (for numbers) -
=VLOOKUP(9.99999999999999E+307,B:B,1)
Change B:B to whatever column you're using.
or this (for text) -
=LOOKUP(2,1/(B:B<>""),B:B)

2

You could try wrapping your text-trimming formula in VALUE(...) to convert the string result to a number.
For example:
=VALUE(RIGHT(A1,LEN(A1)-3))

2

If the second table in your question has the days in row 1 and the names in column J, and if your first table starts in A1, you can use this in cell K2, copy down and to the right
=INDEX($A$1:$A$4,MATCH($J2,B$1:B$4,0))

2

=SUM(COUNTIF(K:K,{"Busy","Hang up","Stopped","Voicemail","Wrong Number","No Answer","IP Phone Offline","Call accepted"}))
If this list is subject to change/expansion then it would make more sense to store the criteria in cells within the worksheet, rather than within the formula itself. For example, if the criteria were in A1:A8, the required formula would ...

2

Your formula is failing because you're referencing an entire column rather than a specific range. This will, naturally, find the most common value to be blank..
You can fix this by using this (extremely volatile) function -
=INDEX(INDIRECT("G1:G"&COUNTA(G:G)),MODE(MATCH(INDIRECT("G1:G"&COUNTA(G:G)),INDIRECT("G1:G"&COUNTA(G:G)),0)))
I don't ...

2

=AVERAGE(OFFSET($A$1,(ROW(A1) - 1) * 5,0,5,1))
I put this formula in B1, copied and pasted down column B.
It is performing an average on a range found using the offset formula.
OFFSET (REFERENCE, ROW OFFSET, COLUMN OFFSET, HEIGHT, WIDTH)
REFERENCE = Absolute reference to A1
ROW OFFSET = Current row number minus 1 times 5
COLUMN OFFSET = 0
HEIGHT = 5
...

1

Why not break up the data. Select column B and go to data tab - text to columns - delimited - comma
Now in B3 put =Concatenate(B1," ",B2) and drag it across.
Now they are paired. You want them back together, use concatenate again.
macro
Sub test()
Dim arrdate As Variant
Dim arrtime As Variant
Dim strcombo As String
arrdate = Split(Range("B1"), ",")
...

1

I haven’t actually tried this with 10,000 rows, but try
=INDEX($S$1:$S$50, MATCH(MAX(COUNTIF($G$1:$G$10000,$S$1:$S$50)), COUNTIF($G$1:$G$10000,$S$1:$S$50), 0))
where the unique state values are in S1:S50. (Naturally this is an array formula;
so you need to type Ctrl+Shift+Enter when you enter it.)
COUNTIF($G$1:$G$10000,$S$1:$S$50) counts how many times ...

1

You are correct, vlookup is the answer - =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
So for you, in the childlist_value you would put (in D1)
=Vlookup(C1,!$A$1:$B$100,2,FALSE)
Or across sheets, in child_list value (Sheet2!B1) -
=Vlookup(A1,Sheet1!$A$1:$B$100,2,FALSE)
This will take the value on child_list (A1), match it to ...

1

Here is one solution. Assume the first data row is row 2. I stuck 100 in as the last data row; make that whatever it is (or you can get fancy with functions and calculate it). Formula for G2:
=IF(AND(C2=4,OFFSET(D2,MATCH(6,C3:C$100,0),0,1)=D2),"Y"," ")
Copy that down for all data rows. Note that the last entry will be #NA. If it matters that it ...

1

No, you can't assign a value to another cell without VBA.
You could do two if formulas like:
A2=IF(B2=20,"True","False")
C2=IF(A2="True","Closed","")
For VBA you could do something like
sub test()
For each c in range("B:B")
If c = 20 then
c.offset(-1,) = "True"
c.offset(1,) = "Closed"
End if
Next c
end sub

1

Sal,
I have a different method that I sometimes use when countif isn't working properly. In Excel, you can use an if statement to return an array of values as well as individual ones which people are not always aware of.
Here is the statement I used to count everything that is "accomplished":
{=SUM(IF(R:R="accomplished",D:D,0))}
Notice the curly ...

1

Try this:
=COUNTIFS(D1:D100,"06:00",R1:R100,"accomplished")
=COUNTIFS(D1:D100,"06:00",R1:R100,"pending")
=COUNTIFS(D1:D100,"18:00",R1:R100,"accomplished")
=COUNTIFS(D1:D100,"18:00",R1:R100,"pending")
=COUNTIFS(D1:D100,"23:59",R1:R100,"accomplished")
=COUNTIFS(D1:D100,"23:59",R1:R100,"pending")

1

As per my comment, you might try something like this:
=COUNTIFS(C4:C111,">="&TIME(18,0,0),C4:C111,"<="&TIME(23,59,0),R2:R109,"Pending")

1

A blank cell is not the same as zero in Excel. Excel recognizes blank as empty rather than a value. If you need a blank cell to be treated as zero in a formula, do something like this (for this example, I'll use A1 as the referenced cell):
if(isblank(a1),0,a1)
Use this in place of a1 in your formula.
EDIT: You received several answers. Both ...

1

FORMULA:
=VALUE(...)
This formula will evaluate to 0 for blanks and valid values will not be altered.

1

Yes, it’s really rather simple. 'Other Sheet'!$B$4:'Other Sheet'!$B$20 is not a valid reference.
So, instead, something like this might work:
=COUNTIF(INDIRECT(ADDRESS(4,2,1,TRUE,"Other Sheet")&":"&ADDRESS(20,2,1,TRUE)),2)
It produces 'Other Sheet'!$B$4:$B$20.

1

You could do something like this:
Col A Col B Col C
Amt Curr Master
$100 CND =IF(B2="USD",A2*1.2,A2)
$100 USD =IF(B3="USD",A3*1.2,A3)
The formula =IF(B2="USD",A2*1.2,A2) will check if column B is in USD, and if it is, it will multiply it by 1.2 (or whatever the exchange rate should be), otherwise it will display the other ...

1

I've manged to do it with multiple IFERROR inside an IFERORR:
=IFERROR(MID(R167,SEARCH("q=",R167)+2,SEARCH("&",R167)-SEARCH("q=",R167)-2),IFERROR(MID(R167,SEARCH("p=",R167)+2,SEARCH("&",R167)-SEARCH("p=",R167)-2),IFERROR(RIGHT(R167,LEN(R167)-FIND("q=",R167)-1),IFERROR(RIGHT(R167,LEN(R167)-FIND("p=",R167)-1),zzzzzz))))

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