Results 1 to 4 of 4

Thread: Mental blank with Excel formula

  1. #1
    Junior Member
    Join Date
    Jan 2008
    Location
    Cranebrook, NSW
    Posts
    143
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Rep Power
    204
    Reputation
    130

    Default Mental blank with Excel formula

    Google is not on my side today (or I haven't had enough coffee).

    I have a table in Excel with the following:

    Column D (Initials of users attributed to a payment)
    Column C (Current balance)
    Column F (Current invoice)
    Column L (List of users alphabetically)
    Column N (Payment type 1)
    Column O (Payment type 2)

    So basically, in column O, I need to count the values which meet the criteria:

    C = F (in the same row) AND D = L (so I can count the payments on a per-user basis)

    I also need to work out the same as above, but in column N where F>C/2.

    =COUNTIF($D$2:$D$28,L2) works perfectly, as does =COUNTIF($C$2:$C$28,$F$2:$F$28). I just can't put it together.

    Help!

  2. The Following User Says Thank You to therufus For This Useful Post:

    irritant (26-03-19)



Look Here ->
  • #2
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    So you want something like a COUNTIF($D$2:$D$28,L2) AND COUNTIF($C$2:$C$28,$F$2:$F$28) when both are true? You may need VBA using If statetment A=true and statement B=true Then count else go to the next statement.
    I'm out of my mind, but feel free to leave a message...

  • The Following User Says Thank You to lsemmens For This Useful Post:

    irritant (26-03-19)

  • #3
    Junior Member
    Join Date
    Jan 2008
    Location
    Cranebrook, NSW
    Posts
    143
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Rep Power
    204
    Reputation
    130

    Default

    Yes. That's exactly what I need.

    Ideally, I'd like it automatically done as opposed to having to run a script, but I'm not opposed to it. I guess I could always put it in the script that populates the spreadsheet from the database.

  • The Following User Says Thank You to therufus For This Useful Post:

    irritant (26-03-19)

  • #4
    LSemmens
    lsemmens's Avatar
    Join Date
    Dec 2011
    Location
    Rural South OZ
    Posts
    10,585
    Thanks
    11,867
    Thanked 7,061 Times in 3,338 Posts
    Rep Power
    3153
    Reputation
    132592

    Default

    Possibly you could get away with IF(formula A, if (formula B, count increment,0),0)
    I'm out of my mind, but feel free to leave a message...

  • The Following User Says Thank You to lsemmens For This Useful Post:

    irritant (28-03-19)

  • Bookmarks

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •