SOLVED
Home

Function Help! (Count/Vlookup/IF)

%3CLINGO-SUB%20id%3D%22lingo-sub-1115541%22%20slang%3D%22en-US%22%3EFunction%20Help!%20(Count%2FVlookup%2FIF)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115541%22%20slang%3D%22en-US%22%3EHi%20everyone!%3CBR%20%2F%3EI'm%20hoping%20I%20can%20get%20some%20help%20writing%20a%20function.%20I%20need%20something%20that%20can%20look%20up%20a%20project%20number%20in%20another%20tab%20and%20count%20associated%20data%20from%20other%20columns%20to%20bring%20into%20the%20new%20tab.%20I%20attached%20a%20sample%20file%20of%20what%20my%20data%20looks%20like%20and%20how%20I%20want%20the%20resulting%20formula%20to%20be%20displayed.%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20ideas%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1115541%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1115615%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20Help!%20(Count%2FVlookup%2FIF)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492141%22%20target%3D%22_blank%22%3E%40AL789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF(ISEVEN(COLUMN(OFFSET(Table1%5B%5B%23Headers%5D%2C%5BProject%20'%23%5D%5D%2CMATCH(A2%2CTable1%5BProject%20'%23%5D%2C0)%2C1%2C1%2CCOUNTA(Data!%241%3A%241)-1)))%2CIF(OFFSET(Table1%5B%5B%23Headers%5D%2C%5BProject%20'%23%5D%5D%2CMATCH(A2%2CTable1%5BProject%20'%23%5D%2C0)%2C1%2C1%2CCOUNTA(Data!%241%3A%241)-1)%26lt%3B%26gt%3B%22%22%2C1%2C0)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECtrl%2BShift%2BEnter%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-1115633%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20Help!%20(Count%2FVlookup%2FIF)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492141%22%20target%3D%22_blank%22%3E%40AL789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFNA(COUNTIFS(Table1%5B%23Headers%5D%2C%22*Name%22%2CINDEX(Table1%2CMATCH(%24A2%2CTable1%5BProject%20%20'%23%5D%2C0)%2C)%2C%22%26lt%3B%26gt%3B%22)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20373px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F165875iFE17313F02FE21AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1115657%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20Help!%20(Count%2FVlookup%2FIF)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115657%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492141%22%20target%3D%22_blank%22%3E%40AL789%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%20if%20this%20is%20a%20very%20large%20data%20set%2C%20I'd%20recommend%20looking%20into%20PowerQuery.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20you%20data%20after%20I%20%22un-pivot%22%20it%20in%20Power%20Query%20and%20pivot%20it%20for%20the%20results%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%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F165878iC2CD34329621D880%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
AL789
Occasional Contributor
Hi everyone!
I'm hoping I can get some help writing a function. I need something that can look up a project number in another tab and count associated data from other columns to bring into the new tab. I attached a sample file of what my data looks like and how I want the resulting formula to be displayed.

Any ideas?
3 Replies
Highlighted

@AL789 

=SUM(IF(ISEVEN(COLUMN(OFFSET(Table1[[#Headers],[Project '#]],MATCH(A2,Table1[Project '#],0),1,1,COUNTA(Data!$1:$1)-1))),IF(OFFSET(Table1[[#Headers],[Project '#]],MATCH(A2,Table1[Project '#],0),1,1,COUNTA(Data!$1:$1)-1)<>"",1,0)))

 

Ctrl+Shift+Enter

 

 

Highlighted
Solution

@AL789 

As variant

=IFNA(COUNTIFS(Table1[#Headers],"*Name",INDEX(Table1,MATCH($A2,Table1[Project  '#],0),),"<>"),"")

image.png

Highlighted

@AL789 

BTW if this is a very large data set, I'd recommend looking into PowerQuery.

 

This is you data after I "un-pivot" it in Power Query and pivot it for the results:

 

clipboard_image_0.png

Related Conversations
Make Share function in Edge more useful
HotCakeX in Discussions on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
Cannot Export App Package Save As for SharePint
LukeA79 in Access on
0 Replies