Highlighted
New Contributor

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

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

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

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

Highlighted

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

@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

Highlighted

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

Try

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

Highlighted

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

@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

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

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

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

@Hans Vogelaar

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