Forum Discussion

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 an...

Formulas and Functions

MVP

Sep 01, 2020

Instead of SUMMA.OM, use PRODUKTSUMMA:

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

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
  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)
  - eddievan
    Copper Contributor
    Sep 02, 2020
    HansVogelaar
    
    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 🙂

Resources