microsoft excel course london - vlookupsumif

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft excel course london - Vlookup/sumif

microsoft excel course london - Vlookup/sumif

resolvedResolved · Low Priority · Version Standard

Andrew has attended:
Excel Advanced course

Vlookup/sumif

I am trying to create a fomulae to look in a range find all rows with a particular date and pull back all amounts that match. This I can do. However I cant add another variable which limits the return to only those fulfilling another criteria i.e a set bank a/c.

How can I nest a vlookup and if/sumif formulae?

RE: vlookup/sumif

Hi Andrew,

Thank you for the question. You are not able to nest the Vlookup and the if/sumif formulae, however, how about looking into the possibility of putting the AND and OR functions into the IF function??

I will give you an example

=IF(OR(G8="NA", "H8="No"), "No bonus", F8*1.5%))

so you would be in the detination cell and if either of the cells are NA or No the cell will return No bonus, if that is not the case it will return whatever F8*1.5% is.

Alternatively you could try

=IF(AND(H8="Yes", F8>12000), "Yes", "")

and the standard IF conditions apply.

See if you can get around it that way

Tracy

RE: vlookup/sumif

How would I get this work over a range.so if you wanted all Yes's in Col A. All 04/06/07 in Col B and the relevant amount in Col C?

RE: vlookup/sumif

=IF(AND(SUM(A3:A5)=20,B3>20/2/75),"Result","Not applicable") You can use the first argument as a range if you like.

Tracy


 

Excel tip:

Editing a formula quickly

If you want to edit a fomrula or text quickly witin a cell instead of the formula bar, you can click either double click in the cell or press the F2 key

View all Excel hints and tips


Server loaded in 0.08 secs.