Excel Formula Help

[Freddie1980]

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?

 

[Freddie1980]

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.

 

[Freddie1980]

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.