# Search:

Type: Posts; User: 63falcondude; Keyword(s):

Page 1 of 6 1

1. ## Thread: Difference between two dates in hours

by 63falcondude
Replies
4
Views
161

### Re: Difference between two dates in hours

Try this in D2:

=(DATE(2018,MID(C2,4,2),LEFT(C2,2))+RIGHT(C2,5)-(DATE(2018,MID(B2,4,2),LEFT(B2,2))+RIGHT(B2,5)))*24

or
...
2. ## Thread: Reference Lookup need for a Name and Date set

by 63falcondude
Replies
3
Views
122

### Re: Reference Lookup need for a Name and Date set

Great! Happy to help.
3. ## Thread: Reference Lookup need for a Name and Date set

by 63falcondude
Replies
3
Views
122

### Re: Reference Lookup need for a Name and Date set

With your first range in A1:E10 and your second range in G1:J7 (headers in row 1), try this in D2:

=LOOKUP(2,1/((G\$2:G\$7=B2)*(H\$2:H\$7<=A2)*((I\$2:I\$7>=A2)+(I\$2:I\$7=""))),J\$2:J\$7)

by 63falcondude
Replies
6
Views
271

### Re: Please I need a quick help

You're welcome. Happy to help.

by 63falcondude
Replies
6
Views
271

### Re: Please I need a quick help

Try this in B16:

=SUMPRODUCT((B\$2:E\$2=A16)*(B\$10:B\$14="x")*(B\$3:E\$7="x"))

by 63falcondude
Replies
4
Views
248

### Re: SUMIF Issue?

Cross-posted here:

https://www.excelforum.com/excel-formulas-and-functions/1212433-sumif-issue.html
7. ## Thread: how do i format date with text in a formula?

by 63falcondude
Replies
3
Views
102

### Re: how do i format date with text in a formula?

Try

TEXT(OFFSET((PIF_REPORT!\$J\$4,1,0,1,1),"mm-dd-yyyy")&" is the date i'm looking for"
8. ## Thread: formula to find the lowest number?

by 63falcondude
Replies
16
Views
410

### Re: formula to find the lowest number?

Just FYI: If you wanted to return the header of the smallest value, you can use this formula:

G2 =INDEX(B\$1:F\$1,MATCH(MIN(B2:F2),B2:F2,0))

This assumes that your data is in A1:F5
9. ## Thread: Multiple identical results with VLOOKUP

by 63falcondude
Replies
1
Views
112

### Re: Multiple identical results with VLOOKUP

If you are looking to take the sum of something, try using the SUMIF function.
10. ## Thread: How to sumproduct cell arrays?

by 63falcondude
Replies
10
Views
477

### Re: How to sumproduct cell arrays?

Cross-posted here:

https://www.excelforum.com/excel-formulas-and-functions/1206520-how-to-sumproduct-cell-arrays.html

by 63falcondude
Replies
4
Views
142

### Re: IF Statement

Maybe something along the lines of this:

=LOOKUP(A1,{"A","B","C","D","E","F"},{1,2,3,4,5,6})

by 63falcondude
Replies
10
Views
280

### Re: Sequence

You're welcome. Glad we could help.

by 63falcondude
Replies
10
Views
280

### Re: Sequence

Sorry, typo...

Should have been

B3 =IF(C3=1,B2+1,B2)

by 63falcondude
Replies
10
Views
280

### Re: Sequence

With 2000 in B2, try this in B3

=IF(C3=1,B2+1,B3)

Copied down.
15. ## Thread: Converting a text date to numnerical date

by 63falcondude
Replies
3
Views
219

### Re: Help needed Converting a text date to numnerical date

You have a couple of options here.

What I would do in this scenario, is highlight the column with the text dates > Data > Text to Columns > Next > Next > Date: DMY > Finish.

Then you can format...
16. ## Thread: Nestled Sum, If and Year

by 63falcondude
Replies
7
Views
507

### Re: Nestled Sum, If and Year

I put the sample data from post #5 into a new worksheet.

Both of the formulas suggested in post #3 gave me 0.006

=SUMPRODUCT((YEAR(1:1)=YEAR(I16))*(6:6))/1000
and...
17. ## Thread: MIN with 3 IF conditions

by 63falcondude
Replies
5
Views
283

### Re: MIN with 3 IF conditions

Personally, I write MIN IF functions like this:

=MIN(IF((condition1)*(condition2)*(condition3),result)) Ctrl Shift Enter

Just swap the comma for a semicolon if your regional settings use ";"...
18. ## Thread: Nestled Sum, If and Year

by 63falcondude
Replies
7
Views
507

### Re: Nestled Sum, If and Year

Try

=SUM(IF(YEAR('Skylights P&L'!1:1)=YEAR(I16),'Skylights P&L'!6:6))/1000 Ctrl Shift Enter

or

=SUMPRODUCT((YEAR('Skylights P&L'!1:1)=YEAR(I16))*('Skylights P&L'!6:6))/1000

Note that you...

by 63falcondude
Replies
5
Views
253

### Re: Conditional Formatting

You're welcome. Happy to help.

by 63falcondude
Replies
5
Views
253

### Re: Conditional Formatting

You're welcome.

Change that second formula (the red formula) to this:

=AND(AND(\$D3="YES",\$E3="YES",\$F3="YES")=FALSE,COUNTBLANK(\$B3:\$F3)<>5)

by 63falcondude
Replies
5
Views
253

### Re: Conditional Formatting

If you want to highlight B3:F3 in Green if =AND(D3="YES",E3="YES",F3="YES") returns TRUE and highlight B3:F3 in Red if =AND(D3="YES",E3="YES",F3="YES") returns FALSE, try this:

Highlight B3:F3 >...
22. ## Thread: Countif similar text values such as 3.1, 3.10, 4.1, 4.10

by 63falcondude
Replies
4
Views
628

### Re: Countif similar text values such as 3.1, 3.10, 4.1, 4.10

COUNTIF sees 3.1 and 3.10 as the same.

Edit: Unless you do what AhoyNC showed in post 4. I've never seen that before.
23. ## Thread: Need help with countif and indirect.

by 63falcondude
Replies
2
Views
236

### Re: Need help with countif and indirect.

Hello and welcome to the forum. I'm glad that you figured out your question and thank you for sharing the solution.

Note that it is important to mention that you are using Google Sheets.
...
24. ## Thread: Formula Help ?

by 63falcondude
Replies
13
Views
748

### Re: Formula Help ?

I'm not saying that this is the answer to your question, but

=SUM(D6:D10+F6:F10)/(D14+F14)

should be

=SUM(D6:D10,F6:F10)/(D14+F14)
25. ## Thread: IF formula multiple criteria

by 63falcondude
Replies
4
Views
240