IF, AND, OR Statements

%3CLINGO-SUB%20id%3D%22lingo-sub-1580185%22%20slang%3D%22en-US%22%3EIF%2C%20AND%2C%20OR%20Statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1580185%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20clients%20with%205%20columns%20of%20different%20ticket%20prices.%26nbsp%3B%20the%20amounts%20are%20%2465%2C%20%2467.90%2C%20%2470%2C%20%2475%2C%20and%20%2480.%26nbsp%3B%20A%206th%20column%20is%20for%20the%20amount%20to%20be%20refunded.%26nbsp%3B%20I%20have%205%20cells%20where%20I%20have%20used%20Excel%20to%20determine%20the%20refunded%20amount%20owed.%26nbsp%3B%20If%20a%20person%20bought%20a%20ticket%20for%20%2465%20then%20they%20are%20refunded%20%2458.16.%26nbsp%3B%20A%20ticket%20for%20%2470%20is%20refunded%20%2462.63%2C%20and%20so%20on.%26nbsp%3B%20Depending%20on%20how%20many%20tickets%20a%20client%20bought%2C%20the%20ticket%20column%20cell%20could%20have%20no%20number%2C%20a%201%2C%20or%20a%202%2C%20or%20a%203%20in%20it.%26nbsp%3B%20A%20client%20could%20have%20a%202%20in%20the%20%2465%20column%20and%20a%202%20in%20the%20%2475%20column.%26nbsp%3B%20I%20am%20trying%20to%20use%20the%20IF%2C%20AND%2C%20OR%20statements%20to%20first%20look%20in%20a%20cell%2C%20does%20it%20have%20a%26nbsp%3B%20number%2C%20if%20yes%2C%20take%20that%20number%2C%20multiply%20the%20number%20by%20the%20correct%20refund%20cell%2C%20go%20to%20the%20next%20column%2C%20see%20if%20it%20has%20a%20number%2C%20if%20yes%20multiply%20it%20by%20the%20correct%20refund%20cell%2C%20if%20no%2C%20go%20to%20the%20next%20ticket%20cell%2C%20and%20so%20on%20until%20all%205%20ticket%20cells%20are%20checked.%26nbsp%3B%20Once%20all%20ticket%20cells%20are%20checked%2C%20add%20up%20each%20ticket%20number%20and%20put%20the%20total%20owed%20in%20the%206th%20column%20amount%20to%20be%20refunded.%26nbsp%3B%20For%20the%20life%20of%20me%2C%20I%20can%20not%20get%20it%20to%20come%20out.%26nbsp%3B%20The%20equation%20would%20be%20in%20the%20OWED%20column.%26nbsp%3B%20Is%20the%20IF%2C%20AND%2C%20OR%20statements%20the%20correct%20to%20use%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1580185%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1580292%22%20slang%3D%22en-US%22%3ERe%3A%20IF%2C%20AND%2C%20OR%20Statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1580292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755497%22%20target%3D%22_blank%22%3E%40Blacktop%3C%2FA%3E%26nbsp%3BNo%20need%20for%20IF%2C%20AND%2C%20OR.%20Try%20the%20following%20in%20H2%20and%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(C2%3AG2%2C%24J%2414%3A%24N%2414)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a list of clients with 5 columns of different ticket prices.  the amounts are $65, $67.90, $70, $75, and $80.  A 6th column is for the amount to be refunded.  I have 5 cells where I have used Excel to determine the refunded amount owed.  If a person bought a ticket for $65 then they are refunded $58.16.  A ticket for $70 is refunded $62.63, and so on.  Depending on how many tickets a client bought, the ticket column cell could have no number, a 1, or a 2, or a 3 in it.  A client could have a 2 in the $65 column and a 2 in the $75 column.  I am trying to use the IF, AND, OR statements to first look in a cell, does it have a  number, if yes, take that number, multiply the number by the correct refund cell, go to the next column, see if it has a number, if yes multiply it by the correct refund cell, if no, go to the next ticket cell, and so on until all 5 ticket cells are checked.  Once all ticket cells are checked, add up each ticket number and put the total owed in the 6th column amount to be refunded.  For the life of me, I can not get it to come out.  The equation would be in the OWED column.  Is the IF, AND, OR statements the correct to use?

1 Reply

@Blacktop No need for IF, AND, OR. Try the following in H2 and copy it down.

 

=SUMPRODUCT(C2:G2,$J$14:$N$14)