1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel Formula Help

Discussion in 'HTML, Graphics & Programming' started by Freddie1980, Jul 9, 2018.

  1. Freddie1980

    Soldato

    Joined: Sep 25, 2009

    Posts: 6,292

    Location: Billericay, UK

    I'm trying to create a stats report but I can't for the life of me workout the formula I need.

    In Cell B1 I want to return the values from the data report for S O'Brien for cash book receipts greater then 30+
    [​IMG]

    Here is the data
    [​IMG]

    I need to do a VLOOKUP on column B for S O'Brien and then do a COUNT or a COUNTIF on column G for all values that are 30+, 60+ and 90+, then a count on column I for everything that equals 'Cbook' and finally a count on column L for values greater then '0.00'

    Any ideas?
     
  2. RoyMi6

    Wise Guy

    Joined: Mar 9, 2010

    Posts: 2,465

    Use a database.
     
  3. Freddie1980

    Soldato

    Joined: Sep 25, 2009

    Posts: 6,292

    Location: Billericay, UK

    Is there a way of getting this formula to work?

    =IF((AND(O2="Bank",R2<0)),"Bank Receipt","Bank Payment",OR(O2="Cbook",R2<0)),"Cash Book Receipt","Cash Book Payment")


    I'm trying to get so if the value in O is = to Bank and the value in R is less then 0 it returns the value of "Bank Receipt", "Bank Payment. I can get this work but I would also like it to do an OR argument whereby if the value in O is Cbook and the value in R is less then 0 it will return the value "Cash Book Receipt" so I don't have to sort the data and do different formulas.
     
  4. PixieLandGirl

    Wise Guy

    Joined: Jul 13, 2009

    Posts: 1,002

    @Freddie1980 - what are you trying to do in the total column?

    [​IMG]

    [​IMG]

    EDIT:

    Entries | # of data lines per person
    Cash Book Receipts | # data lines per person, matching ageing criteria, Cbook
    Cash Book Payments | # data lines per person, Cbook, >0.00
    Bank Receipts | # data lines per person, matching ageing criteria, Bank
    Bank Payments | # data lines per person, Bank, >0.00

    Check 1 | Does Entries = Cash book Receipts + Bank Receipts
     
  5. R.C.Anderson

    Wise Guy

    Joined: Feb 20, 2009

    Posts: 1,755

    Location: Loughborough

    I can't currently have a play with excel to make it work, but I think with a combination of "vlookup", "match" & "count" functions, you can probably get what you want
     
  6. Flanders

    Hitman

    Joined: Nov 2, 2004

    Posts: 530

    Location: London, UK

    If I have understood correctly, you can simply use SUMIFS.
     
  7. Greboth

    Mobster

    Joined: Aug 6, 2010

    Posts: 4,382

    Location: Land of make believe

    It has been a while since I've used heavily used Excel but from memory doesn't the VLOOKUP lookup value have to be in the first column of the table array for it to work properly. However a COUNTIFS formula should work for what you need as it will count when all the criteria are met. Though you may need some further messing around with some IF arguments if you want it to all work in one column.

    As for the formula above, I'm not sure you can write the OR argument that way as (again from memory) Excel won't allow a third argument to the IF statement. The only way I can think of to do it would be a nestled IF statement, something like;

    =IF(AND(O2="Bank",R2<0),"Bank Receipt",IF(AND(O2="Bank",R2>0),"Bank Payment",IF(OR(O2="Cbook",R2<0),"Cash Book Receipt","Cash Book Payment")))

    However your formula (as mine above also) has conflicting arguments. A value can return a TRUE by meeting your AND(O2="Bank",R2<0) while simultaneously meeting your OR(O2="Cbook",R2<0). I assume by the data and labels, a solution to this would be in my above formula simply change the IF(OR(O2="Cbook",R2<0) to IF(AND(O2="Cbook",R2<0)
     
  8. PixieLandGirl

    Wise Guy

    Joined: Jul 13, 2009

    Posts: 1,002

    Have a look at this to see if it helps

    BAO | =Staff1
    Entries | =COUNTIF(DataTable[BAO],Staff1)
    Cash Book Receipts | =SUM(COUNTIFS(DataTable[BAO],Staff1,DataTable[Bank/Cbook],{"Cbook"},DataTable[Ageing],{"30+","60+","90+"}))
    Cash Book Payments | =SUM(COUNTIFS(DataTable[BAO],Staff1,DataTable[Bank/Cbook],{"Cbook"},DataTable[Amount],">"&0))
    Bank Receipts | =SUM(COUNTIFS(DataTable[BAO],Staff1,DataTable[Bank/Cbook],{"Bank"},DataTable[Ageing],{"30+","60+","90+"}))
    Bank Payments | =SUM(COUNTIFS(DataTable[BAO],Staff1,DataTable[Bank/Cbook],{"Bank"},DataTable[Amount],">"&0))
     
    Last edited: Jul 9, 2018
  9. Freddie1980

    Soldato

    Joined: Sep 25, 2009

    Posts: 6,292

    Location: Billericay, UK

    Thanks for the tips, I've added an extra column to the data report and used this formula,

    =IF((AND(O2="Bank",R2<0)),"Bank Receipt",IF(O2="cbook",IF(R2<0,"Cash Book Payment","Cash Book Receipt"),"Bank Payment"))

    After that I can just pivot the whole thing and gives me exactly what i need.