Sum all the values in G4:G21 where they match what is less than or equal to L3 in E4:E21

[lelkes]

Hi!
I'm a beginner in Excel, and learning with the help of Google and Youtube.
I would like your help with solving:

Sum all the values in G4:G21 where they match what is less than or equal to cell L3 in E4:E21

I have tried and failed (= 0)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

is working. But I got several entries in E4:E21 that meet the criteria (<=L3)....

Please help me solve this

Thanks in advance

//Peter

[Haseeb Avarakkan]

Hello Peter,

If you use "<=L3" will consider like text not a cell L3. So change them to "<="&L3

=SUMIF(E4:E21;"<="&L3;G4:G21

[bschwartz_1]

Try:

=SUMIFS(G4:G21,E4:E21,"<="&L3)

[lelkes]

Hello Peter,

If you use "<=L3" will consider like text not a cell L3. So change them to "<="&L3

=SUMIF(E4:E21;"<="&L3;G4:G21

Thanks Haseeb

[newdoverman]

Here is yet another way:

Please Login or Register  to view this content.

[lelkes]

Thanks newdoverman and bschwartz_1

[newdoverman]

You are welcome.

As you can see, in Excel, there is often more than one way to solve a problem.

[lelkes]

I want to elaborate the formula one step further I now wonder why this formula

Please Login or Register  to view this content.

is not working (i.e) gives error, while this one

Please Login or Register  to view this content.

Best regards and thanks in advance
//Peter

[newdoverman]

Your formulae have named ranges that we cannot evaluate without seeing your workbook.

[lelkes]

I see. I upload the workbook. I hope itīs OK that itīs in Swedish

//Peter

[newdoverman]

The formula where the DATEVALUE(DATUM) is present, the name DATUM has not been defined as a named range.

[lelkes]

Thx newdoverman, but I canīt get it working, even if I define it
In actually, I can't get it working at all with DATAVALUE()

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[newdoverman]

Datevalue should have a single value to evaluate .

Datevalue converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code.

[lelkes]

Thx newdoverman

Datevalue should have a single value to evaluate

But isnīt that I try to do with

=IF(DATEVALUE(D4)<=data!F2;"Y";"No")

--> and this donīt work

Are there any workaround to solve that I donīt first need to create a new column with the dates converted to numbers?

//Peter

[newdoverman]

If DATA!f2 is a date, why not just use =IF(D4<=data!f2,"Y","No")

If data!f2 isn't a date, what is it? Without seeing your workbook, it is difficult to tell what is going on.

[lelkes]

both data!F2 and D4 is date. The difference is that F2 is formated in "swedish date" but the date in D4 is formated in USA/English.
Maybee the easiest solution is to format data!F2 as USA/English

[newdoverman]

That is likely the problem. One thing that I do when trouble-shooting date problems is to change all problem dates to the date serial numbers by selecting GENERAL as the format then when the problems have been sorted out, change the format to what I need.