To only use data from a column if the cell in a row contain the first 4 letters

%3CLINGO-SUB%20id%3D%22lingo-sub-1624644%22%20slang%3D%22en-US%22%3ETo%20only%20use%20data%20from%20a%20column%20if%20the%20cell%20in%20a%20row%20contain%20the%20first%204%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1624644%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%20I%20am%20trying%20to%20sum%20data%20from%20a%20large%20data%20base%20containing%20calenderdays.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20one%20tab%20containing%20thses%20columns%3C%2FP%3E%3CP%3EDate%20Hour%20Type%3C%2FP%3E%3CP%3E200831%200%2C5%20Ordinary%20hours%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20tab%20contain%208779%20rows%20and%20many%20varying%20dates%3CBR%20%2F%3E%3CBR%20%2F%3EI%20now%20want%20to%20sort%20them%20by%20Month%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20far%20my%20Function%20looks%20like%20this%2C%26nbsp%3B%3DSUM.IF(DATA!F%3AF%3BN11%3BDATA!C%3AC)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20pic%20%22DATA1.jpg%22%20and%20you%20see%20that%20the%20date%20format%20is%20200103%20as%20in%20Year%2020%20Month%2001%20and%20Day%2003%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20see%20the%20attached%20pic%20%22DATA3.jpg%22%3CBR%20%2F%3EI%20want%20the%20formula%20on%20top%20%22%3DSUMMA.OM%20...%20%22%20to%20only%20return%20the%20data%20in%20column%20on%20pic%20DATA1%20when%20the%20date%20starts%20with%20%222001%22%20(i.e.%20Year%2020%20Month%2001)%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20could%20I%20rewrite%20the%20formula%20in%20the%20fx-field%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20and%20all%20the%20best%3CBR%20%2F%3E%3CBR%20%2F%3EBrgds%3C%2FP%3E%3CP%3EBo%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1624644%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-1624877%22%20slang%3D%22en-US%22%3ERe%3A%20To%20only%20use%20data%20from%20a%20column%20if%20the%20cell%20in%20a%20row%20contain%20the%20first%204%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1624877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F777766%22%20target%3D%22_blank%22%3E%40eddievan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%20SUMMA.OM%2C%20use%20PRODUKTSUMMA%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DPRODUKTSUMMA((HELTAL(Data!F2%3AF10000%2F100)%3DN11)*Data!C2%3AC10000)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1626956%22%20slang%3D%22en-US%22%3ERe%3A%20To%20only%20use%20data%20from%20a%20column%20if%20the%20cell%20in%20a%20row%20contain%20the%20first%204%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHi%20Hans%20and%20thank%20you%20very%20much%20for%20engaging%20in%20my%20problem%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%3EI%20copy%20and%20pasted%20you%20formula%20in%20to%20the%20marked%20cell%20in%20my%20pic%20DATA3.jpg%20but%20unfortunattely%20it%20returned%20the%20error%20shown%20in%20attached%20pic%20DATA4.jpg%3CBR%20%2F%3E%3CBR%20%2F%3EThough%20I%20cannot%20see%20how%20your%20formula%20consider%20the%20value%20%222001%22%20(representing%20Year%2020%20and%20Month%2001)%20in%20cell%20O10%20as%20this%20is%20the%20only%20values%20that%20should%20be%20returned%20as%20a%20sum%20into%20cell%20O11%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20inserted%20the%20pic%20DATA5.jpg%20that%20explains%20better%20the%20data%20in%20my%20previous%20attached%20DATA3.jpg%3CBR%20%2F%3E%3CBR%20%2F%3Ebest%20regards%3C%2FP%3E%3CP%3EBo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1627027%22%20slang%3D%22en-US%22%3ERe%3A%20To%20only%20use%20data%20from%20a%20column%20if%20the%20cell%20in%20a%20row%20contain%20the%20first%204%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F777766%22%20target%3D%22_blank%22%3E%40eddievan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20a%20discrepancy%20between%20your%20original%20description%20and%20your%20screenshots.%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DPRODUKTSUMMA((HELTAL(Data!A2%3AA10000%2F100)%3DO10)*Data!C2%3AC10000)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1627146%22%20slang%3D%22en-US%22%3ERe%3A%20To%20only%20use%20data%20from%20a%20column%20if%20the%20cell%20in%20a%20row%20contain%20the%20first%204%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EWow%20that%20worked%2C%20half%20way%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20returned%20all%20%222001%22%2C%20but%20I%20wanted%20to%20return%20all%20%222001%22%20with%20%22Ordinary%20hours%20only%22%20in%20column%20F%20of%20tab%20DATA%20(see%20pic%20DATA4.jpg).%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ESorry%20for%20not%20being%20clear%20enough%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1627493%22%20slang%3D%22en-US%22%3ERe%3A%20To%20only%20use%20data%20from%20a%20column%20if%20the%20cell%20in%20a%20row%20contain%20the%20first%204%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F777766%22%20target%3D%22_blank%22%3E%40eddievan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20O11%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DPRODUKTSUMMA((HELTAL(Data!%24A%242%3A%24A%2410000%2F100)%3DO%2410)*(Data!%24F%242%3A%24F%2410000%3D%24N11)%3BData!%24C%242%3A%24C%2410000)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20and%20then%20down%2C%20or%20vice%20versa.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1627750%22%20slang%3D%22en-US%22%3ERe%3A%20To%20only%20use%20data%20from%20a%20column%20if%20the%20cell%20in%20a%20row%20contain%20the%20first%204%20letters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1627750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHans%2C%20I%20am%20impressed%2C%20thank%20you%20soo%20very%20much%20...%3CBR%20%2F%3EGreetings%20from%20Sweden%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Dear all, I am trying to sum data from a large data base containing calenderdays.

I have one tab containing thses columns

Date Hour Type

200831 0,5 Ordinary hours

The tab contain 8779 rows and many varying dates

I now want to sort them by Month

So far my Function looks like this, =SUM.IF(DATA!F:F;N11;DATA!C:C)

Please see the attached pic "DATA1.jpg" and you see that the date format is 200103 as in Year 20 Month 01 and Day 03

Please see the attached pic "DATA3.jpg"
I want the formula on top "=SUMMA.OM ... " to only return the data in column on pic DATA1 when the date starts with "2001" (i.e. Year 20 Month 01)

How could I rewrite the formula in the fx-field?

Thanks and all the best

Brgds

Bo 

6 Replies
Highlighted

@eddievan 

Instead of SUMMA.OM, use PRODUKTSUMMA:

 

=PRODUKTSUMMA((HELTAL(Data!F2:F10000/100)=N11)*Data!C2:C10000)

Highlighted

@Hans Vogelaar 

Hi Hans and thank you very much for engaging in my problem

I copy and pasted you formula in to the marked cell in my pic DATA3.jpg but unfortunattely it returned the error shown in attached pic DATA4.jpg

Though I cannot see how your formula consider the value "2001" (representing Year 20 and Month 01) in cell O10 as this is the only values that should be returned as a sum into cell O11?

I inserted the pic DATA5.jpg that explains better the data in my previous attached DATA3.jpg

best regards

Bo

@eddievan 

There is a discrepancy between your original description and your screenshots.

Try

 

=PRODUKTSUMMA((HELTAL(Data!A2:A10000/100)=O10)*Data!C2:C10000)

Highlighted

@Hans Vogelaar 

Wow that worked, half way

It returned all "2001", but I wanted to return all "2001" with "Ordinary hours only" in column F of tab DATA (see pic DATA4.jpg). 

Sorry for not being clear enough

Highlighted

@eddievan 

 

In O11:

 

=PRODUKTSUMMA((HELTAL(Data!$A$2:$A$10000/100)=O$10)*(Data!$F$2:$F$10000=$N11);Data!$C$2:$C$10000)

 

Fill to the right and then down, or vice versa.

Highlighted

@Hans Vogelaar 

Hans, I am impressed, thank you soo very much ...
Greetings from Sweden