Home

If and Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1131134%22%20slang%3D%22en-US%22%3EIf%20and%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1131134%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApologies%20if%20this%20has%20already%20been%20asked%2C%20but%20I%20keep%20looking%20and%20think%20I%20may%20be%20over%20-%20complicating%20things.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20possible%20I%20need%20to%20find%20a%20way%20to%20find%20which%20week%20a%20person%20appears%20and%20what%20session%20they%20will%20be%20taking%20(see%20attached%20file)%2C%20I%20have%20worked%20out%20how%20to%20find%20out%20which%20weeks%20a%20person%20is%20taking%20with%20-%26nbsp%3B%3DIF(AND(%24A%243%3A%24A%2423%3D%24A3%2C%24D%243%3A%24D%2423%3DI2)%2C%24F%243%3A%24F%2423%2C%22%22)%20but%20I%20need%20to%20break%20it%20down%20to%20the%20group%20as%20well.%26nbsp%3B%20So%20I%20need%20to%20be%20able%20to%20say%20x%20person%20is%20taking%20x%20activity%20with%20x%20group%20in%20x%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20great%2C%20please%20feel%20free%20to%20tell%20me%20I%20am%20over%20thinking%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMachala%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1131134%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-1131198%22%20slang%3D%22en-US%22%3ERe%3A%20If%20and%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1131198%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20I%20did%20think%20of%20that%2C%20but%20my%20complication%20is%20that%20I%20may%20need%20to%20upload%20this%20to%20an%20Access%20database%20so%20I%20was%20trying%20to%20avoid%20this%20-%20but%20I%20think%20that%20you%20may%20be%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMachala%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1131184%22%20slang%3D%22en-US%22%3ERe%3A%20If%20and%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1131184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F158100%22%20target%3D%22_blank%22%3E%40Machala%20Sentance%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20I%20don't%20fully%20understand%20what%20you're%20trying%20to%20do%20here%2C%20but%20looking%20at%20the%20data%20and%20seeing%20what%20you've%20written%2C%20I%20do%20wonder%20if%20the%20%3CSTRONG%3EPivot%20Table%3C%2FSTRONG%3E%20capability%20would%20give%20you%20the%20result%20you%20want%2C%20rather%20than%20an%20exotic%20formula.%20The%20%3CSTRONG%3EPivot%20Table%3C%2FSTRONG%3E%20offers%20ways%20to%20take%20a%20database%20such%20as%20you%20have%20and%20summarize%20it%20in%20rows%20and%20columns%20with%20counts%20or%20sums%20in%20the%20intersections.%20Take%20a%20look%20at%20the%20attached%20to%20see%20if%20the%20method%20works--I%20may%20be%20totally%20off%20base%2C%20and%20this%20may%20not%20have%20the%20rows%20and%20columns%20you%20would%20want%2C%20but%20that's%20easily%20changed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1131555%22%20slang%3D%22en-US%22%3ERe%3A%20If%20and%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1131555%22%20slang%3D%22en-US%22%3EHaven't%20worked%20in%20Access%20for%20years%20(two%20decades%2C%20actually%3B%20dating%20myself%20here)%2C%20but%20unless%20I'm%20mistaken%2C%20Access%20probably%20allows%20for%20a%20similar%20kind%20of%20summary%20report.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi All

 

Apologies if this has already been asked, but I keep looking and think I may be over - complicating things. 

 

If possible I need to find a way to find which week a person appears and what session they will be taking (see attached file), I have worked out how to find out which weeks a person is taking with - =IF(AND($A$3:$A$23=$A3,$D$3:$D$23=I2),$F$3:$F$23,"") but I need to break it down to the group as well.  So I need to be able to say x person is taking x activity with x group in x week.

 

Any help would be great, please feel free to tell me I am over thinking this.

 

Thanks

 

Machala

3 Replies
Highlighted

@Machala Sentance 

I suspect I don't fully understand what you're trying to do here, but looking at the data and seeing what you've written, I do wonder if the Pivot Table capability would give you the result you want, rather than an exotic formula. The Pivot Table offers ways to take a database such as you have and summarize it in rows and columns with counts or sums in the intersections. Take a look at the attached to see if the method works--I may be totally off base, and this may not have the rows and columns you would want, but that's easily changed.

Highlighted

Hi@mathetes 

 

Yes I did think of that, but my complication is that I may need to upload this to an Access database so I was trying to avoid this - but I think that you may be right.

 

Thanks

 

Machala

Highlighted
Haven't worked in Access for years (two decades, actually; dating myself here), but unless I'm mistaken, Access probably allows for a similar kind of summary report.
Related Conversations
SharePoint 2013 LIst Filters (by latest date)
dmphil in SharePoint on
0 Replies
Leap year formula
Hattsoff in Excel on
4 Replies
FORMULAS
aayushman_mishra in Excel on
4 Replies