0

I have a data validation cell (F4) with the 12 months, and I have this sumifs to search for certain dates, how can I change the dates based on the cells (F4)value?

=sumifs('sheet01'!C3:C; 
        'sheet01!D3:D; 
         "*"&B5&"*" ; 
        'sheet01'!B3:B ; "<= 1/01/2020"; 
        'sheet01'!B3:B ; ">= 31/01/2020" )

This is for Jan, but if I choose Feb (F4 Cell) to change the dates to 01/04/2020 -30/04/2020. Is any way to do it?

EDIT: The link of the Spreadsheet (Tab: Anvil - 5%) : https://docs.google.com/spreadsheets/d/11mFjBQfnxBztiSWuv4HgQCwe2ZhTJ_njhlLl1ZKOJmE/edit?usp=sharing

Improve this question
4
  • 1
    Keep in mind that we can't see what you see unless you share a link to your spreadsheet (or a copy of it, or a realistic representative sample from it). Without that context, your post does not make sense to me. You mention F4, but that isn't in your sample formula at all. In addition, while I think you meant "Jan" and not "Jun" in your description, you've got "01/02/2020 - 31/02/2020" listed for "Feb"; but February never has 31 days. The more time and care you put into giving accurate, meaningful information, the better your changes will be of getting a working solution.
    – Erik Tyler
    Commented Dec 13, 2020 at 10:09
  • Thank you for your comments, i have edit my post..
    – Nikos Korilos
    Commented Dec 14, 2020 at 7:58
  • 1
    Does this answer your question? How to sum ​​values ​​in a range of dates
    – Tedinoz
    Commented Dec 14, 2020 at 8:57
  • 1
    Welcome. Would you please add a brief description of your search/research efforts as is suggested in How do I ask a good question?. Would you also describe your own efforts to solve your problem.
    – Tedinoz
    Commented Dec 14, 2020 at 8:58

1 Answer 1

Reset to default
0

I made a duplicate of your "Anvil - 5%" sheet (renamed "Erik Help"). There, I first added data validation to F4 (as a list) so that you can only choose the three-letter English month names. This is an extra level of fool-proofing.

Your formulas in C12 and E12 are already set up to SUMIF only entries that fall between the dates in C7 and E7, respectively. So the only thing you really needed was a way to change those two dates to the beginning and ending of the month whose three-letter English month name is shown in F4.

However, you've set your locale to Greece, while your sheet is in English. This adds a layer of complexity, because month names will only be recognized in Greek. This being the case, I placed the following formula in C7:

=IFERROR(DATEVALUE(SWITCH(F4; "Jan"; "Ιαν"; "Feb"; "Φεβ"; "Mar"; "Μαρ"; "Apr"; "Απρ"; "May"; "Μαΐ"; "Jun"; "Ιουν"; "Jul"; "Ιουλ"; "Aug"; "Αυγ"; "Sep"; "Σεπ"; "Oct"; "Οκτ"; "Nov"; "Νοε"; "Dec"; "Δεκ")&" "&1))

IFERROR will return null should an error every occur. SWITCH will switch the three-letter English month name in F4 to the Greek equivalent. DATEVALUE forms a date from that three-letter Greek month name with " 1" added to it. This will always give the first of that month in the current year, since year is left off.

Once this date is in place, the formula for E7 can reference it in order to return the end of that month:

=IF(C7="";;EOMONTH(C7;0))

This simply says, "If C7 is null, do nothing; otherwise, return the end of the month that is zero months away from the date in C7 (i.e., the end of that same month).

Since I set up data validation in F4, it should be impossible for you to ever have anything in F4 that would trigger an error. But it's good practice to have IFERROR in place anyway, in case things change in the future.

One final note: I could have set up a new sheet with the English and Greek three-letter month names and then used VLOOKUP in the C7 formula. But since this is a closed set, I figured it would be easier for you to understand if I just used SWICH directly in the formula.

Improve this answer
2
  • Thank very match! I used the MONTH, but the EOMONTH was the solution!
    – Nikos Korilos
    Commented Dec 14, 2020 at 14:47
  • Happy to hear it, Nikos. Would you now take a moment to mark my solution as "Best Answer"? This helps us mark the issue as resolved. Thanks.
    – Erik Tyler
    Commented Dec 14, 2020 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.