Excel Formula Help

Freddie1980 · 9 Jul 2018 at 09:50

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+

Here is the data

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?

RoyMi6 · 9 Jul 2018 at 10:04

Use a database.

Freddie1980 · 9 Jul 2018 at 12:40

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.

PixieLandGirl · 9 Jul 2018 at 13:09

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

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

R.C.Anderson · 9 Jul 2018 at 13:15

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

Flanders · 9 Jul 2018 at 13:18

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

Greboth · 9 Jul 2018 at 13:21

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)

PixieLandGirl · 9 Jul 2018 at 13:22

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))

Freddie1980 · 9 Jul 2018 at 13:55

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.