Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.92 seconds.

  1. 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))
  2. Replies
    6
    Views
    121

    Re: adding date using date() function

    Try

    =DATE(YEAR(A1)+1,MONTH(A1)+2,DAY(A1)+3)
  3. 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. Replies
    11
    Views
    261

    Re: why giving me N/A?

    You’re welcome
  5. 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. 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. 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?


    With Sheets("DataDump").Range("A1").CurrentRegion
    MsgBox .Address...
  8. 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. 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. 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. 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. 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. Replies
    3
    Views
    231

    Re: Question regarding looking up values

    You're welcome.
  14. 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. Replies
    4
    Views
    315

    Re: Count ALL days in a month, except Sunday

    You're welcome.
  16. 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. Replies
    3
    Views
    166

    Re: Code Works, but only sometimes?

    You're welcome.
  18. 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. Replies
    3
    Views
    231

    Re: Question regarding looking up values

    Try

    MATCH(""" & intPmtFreq & """,MMQSAA,0),
  20. Thread: Adding Zeroes

    by Jonmo1
    Replies
    3
    Views
    215

    Re: Adding Zeroes

    Welcome to the board.

    In File - Options - Advanced.
    UNcheck "Automatically insert a decimal point"
  21. Replies
    3
    Views
    67

    Re: complex if formula

    You're welcome.
  22. Replies
    3
    Views
    67

    Re: complex if formula

    Try

    =IFERROR(INDEX('different tab'!$S:$S,MATCH(A9,'different tab'!$B:$B,0))/J9,"")
  23. Thread: Countif problem

    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. Re: VBA - If two different ranges contain values

    Try

    If Range("A1").Value = "xxx" And Range("A3").Value <> "" Then
  25. 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 2 3 4