# Search:

Type: Posts; User: Jonmo1; Keyword(s):

Page 1 of 6 1

## Search: Search took 0.92 seconds.

by Jonmo1
Replies
6
Views
129

### Re: Sumproduct Average

If all else fails, remember that average = sum / count

so
=sumproduct(--(A:A="Supplier A"),--(Month(B:B)=2),C:C)/sumproduct(--(A:A="Supplier A"),--(Month(B:B)=2))

by Jonmo1
Replies
6
Views
121

### Re: adding date using date() function

Try

=DATE(YEAR(A1)+1,MONTH(A1)+2,DAY(A1)+3)
3. ## Thread: Sum column using NOT equal to criteria

by Jonmo1
Replies
4
Views
122

### Re: Sum column using NOT equal to criteria

Try this

=SUM(B5:B30)-SUMPRODUCT(SUMIF(A5:A30,E7:E11,B5:B30))
4. ## Thread: why giving me N/A?

by Jonmo1
Replies
11
Views
261

### Re: why giving me N/A?

You’re welcome
5. ## Thread: why giving me N/A?

by Jonmo1
Replies
11
Views
261

### Re: why giving me N/A?

#N/A error is still NOT a 0.
You have to test the cell for error

Try

=IF(ISNA(G8),0,IF(OR(G8=0,AB8>=0),0,AB8))
6. ## Thread: Why 1004 Run Time error at Red Text?

by Jonmo1
Replies
8
Views
263

### Re: Why 1004 Run Time error at Red Text?

If the message box produced a range, then the range isn't the problem (it's OK that it's 'larger than' your actual data. Smaller may have been an issue).

So it must be the ctiteria that is the...
7. ## Thread: Why 1004 Run Time error at Red Text?

by Jonmo1
Replies
8
Views
263

### Re: Why 1004 Run Time error at Red Text?

Maybe the 'Current Region' isn't quite what you think it is..

Add this line in there, and what does the message box say?

8. ## Thread: Delete Rows If Contains Certain Multiple Values

by Jonmo1
Replies
7
Views
3,944

### Re: Delete Rows If Contains Certain Multiple Values

Try

Sub delete_exempt()
Dim c As Range, MyVals As Range, SrchRng As Range
Dim i As Long, lr1 As Long, lr2 As long, x

'This is a range containing all the criteria to search for
lr1 =...
9. ## Thread: Delete Rows If Contains Certain Multiple Values

by Jonmo1
Replies
7
Views
3,944

### Re: Delete Rows If Contains Certain Multiple Values

Welcome to the board.

I think a different approach should be used here.
The code in this thread worked because it was assumed the value would only exist once.

You probably need to go row by...
10. ## Thread: Formula - return bottom non-blank value in a range

by Jonmo1
Replies
5
Views
215

### Re: Formula - return bottom non-blank value in a range

Cutting ranges in half...

original list of numbers
1 2 3 4 5 6 7 8 9 10

Lookup cuts it in half, and looks at the last number of each half..
1 2 3 4 5 (Is 5 greater than the lookup value?)
If...
11. ## Thread: Formula - return bottom non-blank value in a range

by Jonmo1
Replies
5
Views
215

### Re: Formula - return bottom non-blank value in a range

You're welcome..
9.99999999999999E+307 is just the scientific notation for the largest number allowed in a cell, also called Bignum.

Lookup assumes your data is sorted in ascending order (it...
12. ## Thread: Formula - return bottom non-blank value in a range

by Jonmo1
Replies
5
Views
215

### Re: Formula - return bottom non-blank value in a range

Finding the furthest down 'numeric' value, try

LOOKUP(9.99999999999999E+307,\$N\$27:\$N49)
13. ## Thread: Question regarding looking up values

by Jonmo1
Replies
3
Views
231

### Re: Question regarding looking up values

You're welcome.
14. ## Thread: Simple SUMIFS going wrong - I'm doing something dumb

by Jonmo1
Replies
1
Views
136

### Re: Simple SUMIFS going wrong - I'm doing something dumb

Welcome to the board.

Try

=SUMIFS('DATA'!C:C, 'DATA'!A:A, 'Results'!A2,'DATA'!B:B,'RESULTS'!B3)
15. ## Thread: Count ALL days in a month, except Sunday

by Jonmo1
Replies
4
Views
315

### Re: Count ALL days in a month, except Sunday

You're welcome.
16. ## Thread: Count ALL days in a month, except Sunday

by Jonmo1
Replies
4
Views
315

### Re: Count ALL days in a month, except Sunday

Which version of Excel are you using?
This is for XL2010+
Assuming A1 is the FIRST day of a given month

=NETWORKDAYS.INTL(A1,EOMONTH(A1,0),11)
17. ## Thread: Code Works, but only sometimes?

by Jonmo1
Replies
3
Views
166

### Re: Code Works, but only sometimes?

You're welcome.
18. ## Thread: Code Works, but only sometimes?

by Jonmo1
Replies
3
Views
166

### Re: Code Works, but only sometimes?

When nesting ranges, and you specify the book/sheet on the outside range, you must also specify it on the inside ranges too.
Also, you can't select a sheet that is not currently active. (you don't...
19. ## Thread: Question regarding looking up values

by Jonmo1
Replies
3
Views
231

### Re: Question regarding looking up values

Try

MATCH(""" & intPmtFreq & """,MMQSAA,0),

by Jonmo1
Replies
3
Views
215

Welcome to the board.

In File - Options - Advanced.
UNcheck "Automatically insert a decimal point"
21. ## Thread: complex if formula

by Jonmo1
Replies
3
Views
67

### Re: complex if formula

You're welcome.
22. ## Thread: complex if formula

by Jonmo1
Replies
3
Views
67

### Re: complex if formula

Try

=IFERROR(INDEX('different tab'!\$S:\$S,MATCH(A9,'different tab'!\$B:\$B,0))/J9,"")

by Jonmo1
Replies
1
Views
131

### Re: Countif problem

Yes, the * is being treated as a wildcard.

Try
=countif(\$A\$1:\$A\$2000;SUBSTITUTE(EG43;"*";"~*"))
24. ## Thread: VBA - If two different ranges contain values

by Jonmo1
Replies
1
Views
162

### Re: VBA - If two different ranges contain values

Try

If Range("A1").Value = "xxx" And Range("A3").Value <> "" Then

by Jonmo1
Replies
2
Views
163

### Re: Sumifs+sumifs+sumifs

That isn't necessarily a bad thing.
I wouldn't put 'length of formula' high on my list of criteria when judging the quality of a formula.
A couple rules of thumb come to mind.
Shorter does not...
Results 1 to 25 of 150
Page 1 of 6 1