Forum Discussion

eddievan's avatar
eddievan
Copper Contributor
Sep 01, 2020

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 

excel
Formulas and Functions

6 Replies

    • eddievan's avatar
      eddievan
      Copper Contributor
      Sep 02, 2020

      HansVogelaar 

      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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 02, 2020

        eddievan 

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

        Try

         

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

Resources