SOLVED

New Member - Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1278142%22%20slang%3D%22en-US%22%3ENew%20Member%20-%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278142%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20just%20signed%20up%20with%20a%20view%20to%20expanding%20my%20excel%20knowledge.%20For%20the%20last%20few%20days%2C%20I%20have%20been%20searching%20for%20a%20formula%20to%20help%20me%2C%20but%20have%20not%20found%20it%2C%20so%20I%20am%20turning%20to%20the%20Excel%20Guru%20Community.%20Thanks%20in%20advance%20for%20all%20help.%3C%2FP%3E%3CP%3EScenario%3A%3C%2FP%3E%3CP%3EI%20have%204%20columns%20on%20a%20spreadsheet%20(E%20to%20H).%20Based%20on%20the%20specific%20text%20in%20Columns%20E%20and%20G%2C%20I%20want%20to%20return%20certain%20values.%20Ideally%2C%20column%20H%20would%20total%20also.%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EIf%20E4%20is%20Not%20App.%20and%20G4%20is%20Issued%2C%20H4%20%3D%20150%3C%2FP%3E%3CP%3EIf%20E4%20is%20Complete%20and%20G4%20is%20Issued%2C%20H4%20%3D%20318%3C%2FP%3E%3CP%3EIf%20E4%20is%20Issued%20and%20G4%20is%20Not%20App.%2C%20H4%20%3D%20168%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20I%20can%20use%20for%20this%20and%20then%20fill%20down%20for%20the%20rows%20below%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20simple%20version%20showing%20the%20scenarios.%20Any%20help%20would%20be%20greatly%20appreciated%2C%20as%20this%20is%20now%20frustrating%20me%20greatly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1278142%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-1278208%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Member%20-%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F607700%22%20target%3D%22_blank%22%3E%40Eddy_the_Surveyor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20defined%20the%20logic%20only%20for%203%20combinations%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20279px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181860i8CAF8CC27CDECF02%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWhat%20formula%20shall%20return%20for%20other%20ones%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1278232%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Member%20-%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply.%20See%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Eddy_the_Surveyor_0-1585911006590.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181861iC23CD2FC5F8484C3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Eddy_the_Surveyor_0-1585911006590.png%22%20alt%3D%22Eddy_the_Surveyor_0-1585911006590.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWith%20Thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1278270%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Member%20-%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F607700%22%20target%3D%22_blank%22%3E%40Eddy_the_Surveyor%3C%2FA%3E%26nbsp%3BPlease%20see%20attached.%20Is%20that%20what%20you%20had%20in%20mind%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1278284%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Member%20-%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20Brilliant.%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEddy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1278287%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Member%20-%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1278287%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F607700%22%20target%3D%22_blank%22%3E%40Eddy_the_Surveyor%3C%2FA%3E%26nbsp%3BThis%20is%20better%2C%20I%20believe.%20Have%20removed%20the%20hard-code%20150%2C%20168%20and%20368.%20Used%20links%20to%20the%20numbers%20above%20the%20table%20in%20stead.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi Community,

 

I have just signed up with a view to expanding my excel knowledge. For the last few days, I have been searching for a formula to help me, but have not found it, so I am turning to the Excel Guru Community. Thanks in advance for all help.

Scenario:

I have 4 columns on a spreadsheet (E to H). Based on the specific text in Columns E and G, I want to return certain values. Ideally, column H would total also.

For example:

If E4 is Not App. and G4 is Issued, H4 = 150

If E4 is Complete and G4 is Issued, H4 = 318

If E4 is Issued and G4 is Not App., H4 = 168

 

Is there a formula I can use for this and then fill down for the rows below?

 

I have attached a simple version showing the scenarios. Any help would be greatly appreciated, as this is now frustrating me greatly.

 

Thanks.

 

5 Replies
Highlighted

@Eddy_the_Surveyor 

You defined the logic only for 3 combinations

image.png

What formula shall return for other ones?

Highlighted

@Sergei Baklan 

 

Thanks for your reply. See below:

 

Eddy_the_Surveyor_0-1585911006590.png

With Thanks,

Highlighted
Best Response confirmed by Eddy_the_Surveyor (New Contributor)
Solution

@Eddy_the_Surveyor Please see attached. Is that what you had in mind?

 

Highlighted

@Riny_van_Eekelen 

 

That's Brilliant. Thank you.

 

Best Regards,

 

Eddy

Highlighted

@Eddy_the_Surveyor This is better, I believe. Have removed the hard-code 150, 168 and 368. Used links to the numbers above the table in stead.