help with formula while ignoring value errors

%3CLINGO-SUB%20id%3D%22lingo-sub-1533776%22%20slang%3D%22en-US%22%3Ehelp%20with%20formula%20while%20ignoring%20value%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533776%22%20slang%3D%22en-US%22%3EHi%20all.%20New%20user%20and%20self%20taught%20excel%20user%20so%20bare%20with%20me...%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20created%20a%20table%20consisting%20of%202%20list%20columns%2C%20one%20with%20names%20and%20another%20with%20hours%2C%20and%20a%20third%20column%20that%20will%20automatically%20populate%20with%20a%20money%20value%20when%20a%20something%20in%20the%20hours%20column%20is%20selected.%3CBR%20%2F%3E%3CBR%20%2F%3EAlex%20-%202%20hours%20-%20%C2%A330.00%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20I%20am%20trying%20to%20do%20is%20create%20a%20second%20table%20beside%20it%20showing%20how%20much%20each%20person%20has%20made%20in%20money%20but%20the%20issue%20I'm%20having...%20sometimes%20in%20the%20first%20table%20a%20person%20will%20be%20selected%20from%20the%20drop%20down%20list%20but%20may%20not%20have%20any%20hours%20selected%2C%20meaning%20the%20money%20column%20will%20show%20%23N%2FA.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20experimented%20with%20SUMIFS%2C%20COUNTIF%20and%20AGGREGATE%20but%20can't%20get%20it%20to%20work.%20Could%20it%20be%20due%20to%20the%20people's%20names%20are%20from%20a%20list%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20just%20need%20to%20tally%20the%20total%20that%2C%20lets%20say%2C%20Alex%20has%20made%20but%20at%20the%20same%20time%20ignore%20the%20value%20errors.%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20help%20or%20any%20formulas%20would%20be%20much%20appreciated%2C%20and%20I%20apologise%20for%20my%20%22new%20guy%22%20attempt%20at%20explaining%20it%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1533776%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-1533801%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%20while%20ignoring%20value%20errors%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F734139%22%20target%3D%22_blank%22%3E%40kmg2712%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20try%20surrounding%20whatever%20formula%20you%20have%20that%20is%20working%20when%20there%20are%20no%20%23N%2FA%20errors....surround%20that%20existing%20formula%20with%20the%20IFERROR%20function.%20It%20will%20end%20up%20looking%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(%3CEM%3Ewhatever_you_have_already%3C%2FEM%3E)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThat%20should%20put%20a%20zero%20in%20instead%20of%20%23N%2FA%3C%2FP%3E%3CP%3EIf%20you'd%20rather%20a%20blank%20space%2C%20then%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(%3CEM%3Ewhatever_you_have_already%3C%2FEM%3E)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20doesn't%20make%20sense%2C%20or%20doesn't%20work%2C%20perhaps%20you%20could%20post%20a%20copy%20of%20your%20sheet%2C%20just%20devoid%20of%20real%20names%20or%20any%20other%20confidential%20info.%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi all. New user and self taught excel user so bare with me...

I've created a table consisting of 2 list columns, one with names and another with hours, and a third column that will automatically populate with a money value when a something in the hours column is selected.

Alex - 2 hours - £30.00

What I am trying to do is create a second table beside it showing how much each person has made in money but the issue I'm having... sometimes in the first table a person will be selected from the drop down list but may not have any hours selected, meaning the money column will show #N/A.

I've experimented with SUMIFS, COUNTIF and AGGREGATE but can't get it to work. Could it be due to the people's names are from a list?

I just need to tally the total that, lets say, Alex has made but at the same time ignore the value errors.

Any help or any formulas would be much appreciated, and I apologise for my "new guy" attempt at explaining it

Many thanks.
2 Replies

@kmg2712 

 

You could try surrounding whatever formula you have that is working when there are no #N/A errors....surround that existing formula with the IFERROR function. It will end up looking something like this:

=IFERROR(whatever_you_have_already),0)

That should put a zero in instead of #N/A

If you'd rather a blank space, then

=IFERROR(whatever_you_have_already),"")

 

If that doesn't make sense, or doesn't work, perhaps you could post a copy of your sheet, just devoid of real names or any other confidential info.

That worked. Thanks a lot!