Log In
Register
Home
Active Member
Dashboards
Advanced Excel Formulas
August 14, 2019
Offline
Dear all,
I am having issue building a formula that should be simple. Still, it is not working and I hope one of you will be able to help.
Data are in a table. Below is the function :
SUMIFS(Table8[Facture Marchandise HT](sum range ok);Text(Table8[Date Devis];"m")(criteria range which is the issue);COLUMN('DashBoard 2019'!G:G)(criteria chosen ok))
My criteria range (Date devis) is a list of date. I would like to use only the month as my criteria is a number. So I thought that I would use the function text with "m" as the format. But it is not working. Anyone has a clue why ?
Am trying to avoid recreating an additionnal column with the month in my database ...
Hope I have been clear enough, I have added a file, hope it helps clarifying.
Thanks a lot, if anyone can help, would be great.
Nicolas
VIP
April 21, 2015
Offline
I think this has to do with the TEXT function, not being able to use it for a range (ie E1:E7). In your column D the function refers only to one cell at a time, that's the difference. Maybe somebody here knows a substitute for you to use. I can't figure out how to do it.
Frans
July 16, 2010
Offline
Hi Nicolas,
Try this formula:
=SUMIFS(F1:F7,E1:E7,">="&DATE(2012,3,1),E1:E7,"<="&DATE(2012,3,31))
If you want the date field to be dynamic then you can replace DATE(...) with a reference to a cell or something else.
More on working with dates as criteria in SUMIFS.
Mynda
1 Guest(s)
