SOLVED
Home

IF Function

%3CLINGO-SUB%20id%3D%22lingo-sub-855782%22%20slang%3D%22en-US%22%3EIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855782%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20please%20have%20help%20with%20this%20one..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20do%20the%20following%3A%3C%2FP%3E%3CP%3E-%20Column%20M%20on%20Tab%201%20has%20dates%20relating%20to%20a%20figure%20Column%20G%2C%20which%20has%20a%20project%20name%20in%20Column%20B.%3C%2FP%3E%3CP%3E-%20I%20want%20Tab%202%20to%20have%20January%20to%20December%20along%20the%20top%20row%3C%2FP%3E%3CP%3E-%20Tab%202%20to%20have%20Project%20Names%20from%20Tab%201%20Column%20B%20down%20column%20A%3C%2FP%3E%3CP%3E-%20Figures%20from%20Tab%201%2C%20Column%20G%20relating%20to%20the%20dates%20in%20Column%20M%20input%20into%20the%20correct%20month%20column%20in%20Tab%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-855782%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-855789%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408975%22%20target%3D%22_blank%22%3E%40johnjamescassidy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20give%20this%20a%20try...%3C%2FP%3E%3CP%3EOn%20Gateway%20Sheet%2C%3C%2FP%3E%3CP%3EIn%20B2%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT(('Original%20List%20'!%24B%242%3A%24B%2421%3D%24A2)*(TEXT('Original%20List%20'!%24M%242%3A%24M%2421%2C%22mmmm%22)%3DB%241)*('Original%20List%20'!%24G%242%3A%24G%2421))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20copy%20it%20across%20and%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855792%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855792%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20get%20this%20to%20copy%20down%20and%20across%20to%20take%20into%20account%20the%20additional%20figures%20and%20dates%20per%20months.%3C%2FP%3E%3CP%3EI%20would%20like%20all%20the%20amounts%20per%20month%2C%20per%20project%20to%20come%20across%20to%20the%202nd%20tab.%3C%2FP%3E%3CP%3EAnd%20then%20totaled%20per%20month.%3C%2FP%3E%3CP%3EPlease%20see%20attached.%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855802%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855802%22%20slang%3D%22en-US%22%3E%3CP%3ELike%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855804%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855804%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20that%20worked.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20additional%20query%20for%20the%20same%20sheet%2C%20in%20the%20same%20vein.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20Tab%203%20to%20be%20populated%20with%20the%20Project%20Names%20in%20Column%201%20and%20corresponding%20Project%20Start%20Dtates%20organised%20to%20the%20correct%20monthly%20column%20using%20the%20dates%20from%20Tab%201%2C%20Column%20N.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ECan%20you%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855911%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855911%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Project%20Start%20Date%20is%20column%20M%20not%20column%20N%20but%20since%20you%20mentioned%20column%20N%20in%20your%20requirement%20I%20have%20used%20column%20N%20reference%20in%20the%20formula.%20If%20it's%20not%20column%20N%20but%20column%20M%2C%20change%20the%20column%20reference%20in%20the%20formula%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Project%20Start%20Dates%20Sheet%2C%3C%2FP%3E%3CP%3EIn%20B2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX('Original%20List%20'!%24N%242%3A%24N%2421%2CMATCH(1%2CINDEX(('Original%20List%20'!%24N%242%3A%24N%2421%26lt%3B%26gt%3B%22%22)*('Original%20List%20'!%24B%242%3A%24B%2421%26amp%3BTEXT('Original%20List%20'!%24N%242%3A%24N%2421%2C%22mmmm%22)%3D%24A2%26amp%3BB%241)%2C)%2C0))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20copy%20it%20across%20and%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20look%20at%20the%20attached%20and%20let%20me%20know%20if%20that's%20what%20you%20were%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20takes%20care%20of%20your%20question%2C%20please%20mark%20this%20post%20as%20an%20Accepted%20Answer%2FResponse%20which%20will%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3CP%3EYou%20may%20also%20hit%20the%20like%20button%20under%20the%20posts%20which%20helped%20you%20to%20resolved%20your%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856076%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856076%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408975%22%20target%3D%22_blank%22%3E%40johnjamescassidy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20added%20Power%20Query%20solution%20for%20exactly%20the%20same%2C%20only%20for%20illustration.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
johnjamescassidy
Occasional Contributor

Hi,

 

Can I please have help with this one..

 

I am looking to do the following:

- Column M on Tab 1 has dates relating to a figure Column G, which has a project name in Column B.

- I want Tab 2 to have January to December along the top row

- Tab 2 to have Project Names from Tab 1 Column B down column A

- Figures from Tab 1, Column G relating to the dates in Column M input into the correct month column in Tab 2.

 

Sheet attached.

 

John

 

6 Replies
Highlighted

@johnjamescassidy 

Please give this a try...

On Gateway Sheet,

In B2

=SUMPRODUCT(('Original List '!$B$2:$B$21=$A2)*(TEXT('Original List '!$M$2:$M$21,"mmmm")=B$1)*('Original List '!$G$2:$G$21))

and copy it across and down.

@Subodh_Tiwari_sktneer 

I cannot get this to copy down and across to take into account the additional figures and dates per months.

I would like all the amounts per month, per project to come across to the 2nd tab.

And then totaled per month.

Please see attached.

John

Highlighted
Solution

Like this?

 

Highlighted
Spoiler
 

@Subodh_Tiwari_sktneer 

Thank you that worked. 

 

I have an additional query for the same sheet, in the same vein.

 

I need Tab 3 to be populated with the Project Names in Column 1 and corresponding Project Start Dtates organised to the correct monthly column using the dates from Tab 1, Column N.


Can you help?

 

John

 

Highlighted

Hi John,

 

The Project Start Date is column M not column N but since you mentioned column N in your requirement I have used column N reference in the formula. If it's not column N but column M, change the column reference in the formula used.

 

On Project Start Dates Sheet,

In B2

=IFERROR(INDEX('Original List '!$N$2:$N$21,MATCH(1,INDEX(('Original List '!$N$2:$N$21<>"")*('Original List '!$B$2:$B$21&TEXT('Original List '!$N$2:$N$21,"mmmm")=$A2&B$1),),0)),"")

and copy it across and down.

 

Please look at the attached and let me know if that's what you were trying to achieve.

 

If that takes care of your question, please mark this post as an Accepted Answer/Response which will mark your question as Solved.

You may also hit the like button under the posts which helped you to resolved your question.

 

 

Highlighted

@johnjamescassidy 

 

I added Power Query solution for exactly the same, only for illustration.

Related Conversations
Make Share function in Edge more useful
HotCakeX in Discussions on
2 Replies
function talking to table storage
donquijote in Compute on
0 Replies
Calculated column help
gopalaraoa in SharePoint on
1 Replies
Excel If Functions
Mfouad2255 in Excel on
10 Replies
Combining IF and INDEX functions
Hans van Deursen in Windows PowerShell on
2 Replies