Need help with Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-3192125%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192125%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20am%20creating%20annual%20review%20docs%20for%20patient%20numbers%20and%20could%20just%20do%20with%20a%20little%20help%20in%20creating%20a%20formula.%20we%20have%20wards%20with%203%20shifts%20x%20365%20days%20of%20the%20year%20and%20I%20need%20to%20generate%20a%20figure%20for%20each%20status%20of%20wellbeing%20in%20each%20ward.%26nbsp%3B%20we%20have%205475%20line%20of%20info%20across%205%20different%20status%20types%20so%20in%20column%20B%20status%20(SNCT%200%3B%20SNCT%201a%3B%20SNCT%201b%3BSNCT%202%20%26amp%3B%20SNCT%203.%20and%20then%20column%20G%20count%20of%20patients.%26nbsp%3B%20I%20need%20to%20create%20a%20little%20table%20giving%20the%20total%20number%20of%20patients%20on%20all%20three%20shifts%20across%20365%20days%20and%20I%20then%20need%26nbsp%3B%20a%20sub%20total%20for%20each%20status%20snct%200%20-%201a%20-%201b%20-%202%20%26amp%3B%203.%26nbsp%3B%20Each%20status%20has%201095%20lines%20out%20of%20the%205475%20lines%20except%20in%20a%20leap%20year%20when%20it%201098.%26nbsp%3B%20Currently%20we%20have%20to%20filter%20each%20status%20type%2C%20auto%20sum%20column%20G%20and%20then%20repeat%205%20times%20for%20each%20of%20180%20wards.%26nbsp%3B%20I%20need%20to%20have%20a%20formula%20that%20says%20if%20column%20B%20%3D%20SNCT%2001%20then%20give%20me%20a%20sub%20total%20for%20all%20all%20the%201095%20cells%20in%20column%20G.%20Any%20help%20gratefully%20received.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3192125%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192353%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313334%22%20target%3D%22_blank%22%3E%40michaelfarmer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETake%20a%20look%20at%20pivot%20tables.%20They%20let%20you%20slice%20and%20dice%20information%20in%20various%20ways.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi am creating annual review docs for patient numbers and could just do with a little help in creating a formula. we have wards with 3 shifts x 365 days of the year and I need to generate a figure for each status of wellbeing in each ward.  we have 5475 line of info across 5 different status types so in column B status (SNCT 0; SNCT 1a; SNCT 1b;SNCT 2 & SNCT 3. and then column G count of patients.  I need to create a little table giving the total number of patients on all three shifts across 365 days and I then need  a sub total for each status snct 0 - 1a - 1b - 2 & 3.  Each status has 1095 lines out of the 5475 lines except in a leap year when it 1098.  Currently we have to filter each status type, auto sum column G and then repeat 5 times for each of 180 wards.  I need to have a formula that says if column B = SNCT 01 then give me a sub total for all all the 1095 cells in column G. Any help gratefully received. 

1 Reply

@michaelfarmer 

Take a look at pivot tables. They let you slice and dice information in various ways.