# Search:

Page 1 of 3 1

1. ## Thread: Help needed with a formula combining lookup and if statement - Excel 2010

Replies
4
Views
90

### Hi neil =SUMIF(B5:I5,">0",\$B\$3:\$I\$3)

Hi neil

=SUMIF(B5:I5,">0",\$B\$3:\$I\$3)
2. ## Thread: Help needed with a formula combining lookup and if statement - Excel 2010

Replies
4
Views
90

### Hi neil Perhaps the SUMIF, in K5 & copy down!...

Hi neil

Perhaps the SUMIF, in K5 & copy down!

=SUMIF(B5:I5,"<>N/A",\$B\$3:\$I\$3)
3. ## Thread: Calculating time difference in military time

Replies
14
Views
610

### Hi Or maybe a formula if you do not want to...

Hi

Or maybe a formula if you do not want to use code.

=MOD((--TEXT(B2,"00\:00\:00"))-(--TEXT(A2,"00\:00\:00")),1) copy down and format as "hh:mm:ss or [h]:mm:ss".
4. ## Thread: SUM() excluding errors

Replies
11
Views
2,066

### Martin If you have Excel 2010> ...

Martin

If you have Excel 2010>

=AGGREGATE(9,6,[range])

Ignores errors and text!

Elegant. :)
5. ## Thread: SUM() excluding errors

Replies
11
Views
2,066

### Or =SUMIF([range],">0") I see you have moved...

Or =SUMIF([range],">0")

I see you have moved from Fukuoka! :)

Kevin
6. ## Thread: convert id number into date

Replies
5
Views
1,374

### Hi Perhaps something along these lines. In...

Hi

Perhaps something along these lines.

In B1: =DATE("19"&LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)) & format as dd mmmm yyy
In C1: =LOOKUP(RIGHT(A1,1)+0,{1,2,3},{"johor","kedah","kelantan"})
...
7. ## Thread: convert id number into date

Replies
5
Views
1,374

### Hi Assuming in A1: 801107, in B1 and format...

Hi

Assuming in A1: 801107, in B1 and format the cells "dd mmmm yyy", without the quotes.

=DATE("19"&LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
8. ## Thread: finish date formula needed

Replies
3
Views
510

### Another version using EDATE ...

Another version using EDATE

=EDATE(B2,(A2*12)-1)
9. ## Thread: COUNTIF but if Hi-Lited Don't

Replies
9
Views
1,670

### I know you guys on here are not bothered about...

I know you guys on here are not bothered about cross posting!

But this guy (JOB-Dave) has even used the code in post #3 and not even given the credit to Maudibe.
...
10. ## Thread: COUNTIF but if Hi-Lited Don't

Replies
9
Views
1,670

### Hi JOB-Dave Is there conditional formatting...

Hi JOB-Dave

Is there conditional formatting involved! If so you can use that to achieve the count.
11. ## Thread: Find last value in a column

Replies
15
Views
985

### Hi zeddy Strange that, as both files work for...

Hi zeddy

Strange that, as both files work for me!

Kevin

Replies
16
Views
1,535

### @ HowardC Try cleaning your file up for...

@ HowardC

Try cleaning your file up for starters!!
13. ## Thread: Find last value in a column

Replies
15
Views
985

### Hi zeddy See the attached file. Kevin

Hi zeddy

See the attached file.

Kevin
14. ## Thread: Find last value in a column

Replies
15
Views
985

### Hi lizat Try the LOOKUP function:...

Hi lizat

Try the LOOKUP function:
=LOOKUP(2,1/(C:C<>""),C:C)

Kevin
15. ## Thread: Vlookup where numbers on one sheet are text

Replies
3
Views
424

### Hi HowardC Perhaps another way and perhaps...

Hi HowardC

Perhaps another way and perhaps shorter, is to convert the lookup number to text, I have also wrapped in a IFERROR as you uploaded a .xlxs file.

=IFERROR(VLOOKUP(C1&"",'Imported...
16. ## Thread: Help with a solution for calculating incentives

Replies
8
Views
894

### Hi Eagle01 SUMIFS is all you need for that in...

Hi Eagle01

SUMIFS is all you need for that in your file that you posted in post #1
In C6 and copy down:
...
17. ## Thread: Calculating Time from Custom date formats

Replies
3
Views
572

### Hi bnorthby Or =B4-A4 & format the cells as...

Hi bnorthby

Or =B4-A4 & format the cells as [m]
18. ## Thread: A lookup to take into account 2 column values and 1 header value

Replies
12
Views
1,123

### Hi Personally I think most people on the...

Hi

Personally I think most people on the forums don't have really huge files so speed, time etc is not a big problem, but I could be wrong.

@ zeddy. See the attached link with regards to...

Replies
12
Views
1,123

Hi zeddy

Thank you.
20. ## Thread: A lookup to take into account 2 column values and 1 header value

Replies
12
Views
1,123

### Hi stimpsond1 INDEX & MATCH should achieve the...

Hi stimpsond1

INDEX & MATCH should achieve the result you are after. See the attached file. The formula in D8 is an array formula and entered as CTRL + SHIFT + ENTER not just enter, then copy down.
21. ## Thread: Wrap Text function treats negative sign like a hyphen

Replies
5
Views
1,221

### Hi yoree Why don't you keep the Lat/Long in...

Hi yoree

Why don't you keep the Lat/Long in their own cells, at least they will be numbers and you can still use them. Adding "," etc they are then text!

Kevin
22. ## Thread: conditional formatting to display a currency symbol based on text

Replies
9
Views
2,452

### Hi Martin In Excel 2007 & 2010 you can. I have...

Hi Martin

In Excel 2007 & 2010 you can. I have noticed that you uploaded xls file, not possible in 2003!

Kevin
23. ## Thread: conditional formatting to display a currency symbol based on text

Replies
9
Views
2,452

### Hi stimpsond1 You could also use Conditional...

Hi stimpsond1

You could also use Conditional Formatting, select column B or range of cells in column B you wish to apply the rules.

For USD: =\$A1="USD" format as US dollars.
For GBP :...
24. ## Thread: Function other than VLookup

Replies
10
Views
702

### Hi ab2537 I forgot to add to post #7 that it...

Hi ab2537

I forgot to add to post #7 that it is an Array formula. You need to CTRL + SHIFT + ENTER to enter the formula, not just enter, then copy across and down.

Kevin