Excel Formula Help

Soldato
Joined
25 Sep 2009
Posts
9,616
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+
bbwHPVj.jpg

Here is the data
2vJ7x8V.jpg

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?
 
Soldato
OP
Joined
25 Sep 2009
Posts
9,616
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.
 
Associate
Joined
13 Jul 2009
Posts
1,133
Location
Cambs
@Freddie1980 - what are you trying to do in the total column?

pblzNO6.png

UIneSob.png

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
 
Soldato
Joined
6 Aug 2010
Posts
5,629
Location
Birmingham
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)
 
Associate
Joined
13 Jul 2009
Posts
1,133
Location
Cambs
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:
Soldato
OP
Joined
25 Sep 2009
Posts
9,616
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.
 
Back
Top Bottom