Sumif / Sumifs in a Dynamic Table

[Cellardoor]

Hello Everyone and thank you for your attention.

I'm with a small issue regarding the sumif(s) function.

1st, the table:

Headers - Total, 31-Dec-14, 31-Dec-15, 31-Dec-16
Values - (sumif...), 10 20 30

So, when im in a "normal" excel spreadsheet, i can easaly use the sumif function to sum the values bigger than 31-Dec-14 (for instance)

The problem is when i convert all this data to a "Dynamic table", the sumif function doesn't work. It seems that the dates are converted into text and, for that reason, the sumif criteria doesn't work.

What am i doing wrong?

Hope its clear the question.

Many Thanks

 

[BBD]

Hi Cellador,

Below is a sample file which you can refer to build the formula.

 

[BBD]

Hi Cellador,

Sorry. I didnt read it clearly. I am guessing that the problem that you are facing is that when your dynamic table's range changes, your sumif function's lookup range does not increase. Its best if you upload the file, so that we can have a look at it.

 

[Cellardoor]

Hello BBD. Thanks for the answer.

Here is the example of what im saying. In the 1st table you'll see that my criteria range are the dates, in order to achieve the values < or > of a certain date.

But, when i convert to a dynamic table... I cant use the sumif...

Thank you

 

[BBD]

Hi Celladoor,

I am seeing this issue for the first time too. Maybe the experts might be able to help you.

Here is the workaround I did which I really dont know if it will serve your purpose.

Here is what I did. I converted the data to table and unchecked the option where it says that the data has headers. So a default set of headers appear. And you can hide those by unchecking Header Row in Table Tools > Desing.

 

[Cellardoor]

...I've done a lot of research...and no results...

But maybe the only way to solve it is to do as you suggest.

till better answer, i will use this.

Thank you

 

[SirJB7]

Hi, Cellardoor!

Interesting issue. Give a look at the uploaded file.

1st of all I want to thank @Kyle McGhee for having refreshed my mind about structured tables referencing:
http://chandoo.org/forum/threads/excel-table-and-offset.13872/#post-82365

Now to the point.

Usually it's equivalent to use SUMIFS and SUMPRODUCT (exception made of the notation but this isn't the point), but not with these issues:
a) Headers in tables are strings, not numbers, not dates, nor nothing but strings, so you'll have to use the proper conversion function to deal with them (in this case YEAR(DATENUMBER()) and VALUE())
b) For somewhat unknown mystery, even using SUMIFS with the cautions of a), and even if debugging the formula step by step to its elementary items, the whole combo refused to work, so went to the plan B, SUMPRODUCT.
c) I used 2 adjacent tables, blue and grape.
d) For 2nd table, I changed the header content from date values formatted as mmm.yy (which my Spanish Excel version refused to accept while texts, even being fine with those of row 3 while dates) to strings in the form of 01/mm/yyyy that will accept the use of DATENUMBER function. If you don't have international information exchanges you may want to keep your old notation.
d) For 1st table, I had to play a little trick to avoid border conditions (I had to choose if towards the end 2016/2017 which would overlap with a valid date -1st column of 2nd table- or towards the beginning 2013/2014 which it stays within the same table: I chose this). The trick is to define the header of the column previous to the 1st year column data (2014), i.e., the Loan column, as 2013 and format the cell as ";;;"Loan"" (externally unquoted), otherwise the totals for 1st year will display error.

Said so, this is the formula:
=SUMAPRODUCTO((Tabla2[@])*(AÑO(FECHANUMERO(Tabla2[#Encabezados]))<=VALOR(INDICE(Tabla1[#Encabezados];1;COLUMNA()-COLUMNA([2013])+1)))*(AÑO(FECHANUMERO(Tabla2[#Encabezados]))>VALOR(INDICE(Tabla1[#Encabezados];1;COLUMNA()-COLUMNA([2013]))))) -----> in english: =SUMPRODUCT((Tabla2[@])*(YEAR(DATEVALUE(Tabla2[#Headers]))<=VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013])+1)))*(YEAR(DATEVALUE(Tabla2[#Headers]))>VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013])))))

Just advise if any issue.

Regards!

 

[Cellardoor]

SirJB7 thank you for your help.

Till better answer, i believe it's impossible to do what i wish to do .

The formula you've wrote, it's "almost" correct ...but it works as a "no better answer" (sorry, but i don't know the English expression for "martelada" - (Portuguese) ).

I also Believe that what i've asked goes out of the scope of these dynamic tables, and that's why it is impossible to manage it in a simple and fast way.

But, if i reach any conclusion, i will share it here, for sure.

By the way, where should i put this code????

Many thanks for the help

Cellardoor

 

[SirJB7]

Hi, Cellardoor!

I disagree. You can now do with structured tables what you initially did with range lists, and what you couldn't before posting here. You only have follow Darwin's motto (*) and choose the right tool, a precision screwdriver instead of a "martelo" (hammer). With both tools you'd probably end getting the "parafuso" (screw) tucked into the wood, but at what cost?

If you change from range lists to structured tables you should follow the rules of the new environment, not those from the old. I think I clearly explained why and how to do it (change SUMIFS by SUMPRODUCT, nothing from the outer space; convert the headers that are treated as text into dates and numbers, via YEAR, DATENUMBER & VALUE functions; replace the old references to ranges used with SUMIFS by the new references to the structured tables used with SUMPRODUCT).

And everything gets displayed as in the original. So it works for me.

Now if you still think that what you wished to do it's impossible, I should say that if you still insist on using a martelo instead of a precision screwdriver, yes, it's impossible.

Regarding:

By the way, where should i put this code????

I don't understand about what code are you talking about: there's absolutely no code at all (file's still a .xlsx), I wrote only one formula, as usual in my local Spanish Excel version so as to don't make any mistake and in the English version, for the worldwide community. In English:
D22: =SUMPRODUCT((Tabla2[@])*(YEAR(DATEVALUE(Tabla2[#Headers]))<=VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013])+1)))*(YEAR(DATEVALUE(Tabla2[#Headers]))>VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013])))))
... and drag & drop across thru F22.
BTW, in the uploaded file the formulas are yet written and copied, otherwise you wouldn't retrieve the same original values: 60/75/6, 10/20/30, 6/2/4.

Hope it helps.

Regards!

(*) http://chandoo.org/forum/threads/4-in-a-row.13955/#post-82561

 

[Cellardoor]

Hey Man...relax...it's just my opinion .

I understand your formula...But tell me honestly, what is the simplest way?

- D22: =SUMPRODUCT((Tabla2[@])*(YEAR(DATEVALUE(Tabla2[#Headers])).......

or

- SUMIFS(Range;CriteriaRange1,Criteria,Criteriarange2,Criteria2), and drag it to the cells we want (with the $ $ placed correctly)? We only need to write it once.

see?

Other point:

- If you check in the file you've sent, the dates were written as "text". The point here is that i need to have those dates as "dates", for use them in my calculations (interests calculations ) or just to format them as i want. It gives more flexibility, no?

But if there is a way to let the dates as "dates", then your formula works perfectly

See my point?

Maybe my conception of the tables is not the best and probably i'm trying to mix things that are not "mixable"...

And, more as a Global opinion, i think that, whenever possible, we shouldn't replace numbers by letters. When we do that, the flexibility goes away.

But, that's just my way to work. And im total flexibly to change it.

Oh and the code...Sorry, i've seen the

Tip:
Use

 &

tags to embed your VBA Macros

...no comments...Lol

Cheers

 

[SirJB7]

Hi, Cellardoor!

Take it easy, man, I won't lose my calm because of a different opinion -which is always enriching, a priori- on an Excel subject, maybe I'd only add a slight quote of vehemence.

It's just a matter of concepts: if you used to drive cars with manual shifts you couldn't expect to keep on playing with the gear lever while you drive; use it for parking, for direct or stop.

I don't know if the old way (ranges) is easier, yes it's more intuitive, but I think that's because we're used to play with cell references and not columns and rows, this is more likely a database approach and sooner or later Excel will move towards that.

I accept, realize and agree that being text (and no workaround availabe, AFAIK) is a big issue, but Redmond guys don't put us a gun in our heads to use structured tables, and maybe in next versions they fix this up, who knows. As with almost everything in technology, they have their pros & their cons, it's up to oneself to evaluate if and when switching from an older scenario to a newer one.

If you ask me what would I do, and assuming that there're no company or IT policies that might skew the things, if it's only a matter of this workbook, I'd choose either method as I always can hold a helper line to hold the values "o velho" (the older way) if I go for the structured version; now if this is part of a group of files that should be coherent and equally developed, I'd follow that path.

Now if it's new stuff, I decidedly would follow Darwin's advice.

Regards!

 

[Ram Mher]

Dear freind , i would like to learn the formula which you have used in your file. I know the use of Sum ifs function but in your formula i observed that you have some new things which is new for me. So please describe the things for following items
1. "<=" why you have used these symbol in Apstrophe
2. In your formula, What is the use of & with <=
3. Describe the complete formula you have used for 2015

 

[SirJB7]

Hi, Ram Mher!

The formula for D22, to be dragged and dropped thru F22:

=SUMPRODUCT((Tabla2[@])*(YEAR(DATEVALUE(Tabla2[#Headers]))<=VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013])+1)))*(YEAR(DATEVALUE(Tabla2[#Headers]))>VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013])))))

It's a 3 term SUMPRODUCT, multiplied by "*" (unquoted) instead of list separated by ";" (unquoted):

a) 1st term
(Tabla2[@])
Just the table with the grape title

b) 2nd term
(YEAR(DATEVALUE(Tabla2[#Headers]))<=VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013])+1)))
The condition for matching the columns of grape table less or equal than the related year of blue table, i.e., year of grape dates <= blue years.

c) 3rd term
(YEAR(DATEVALUE(Tabla2[#Headers]))>VALUE(INDEX(Tabla1[#Headers],1,COLUMN()-COLUMN([2013]))))
The condition for matching the columns of grape table greater than the related -previous- year of blue table, i.e., year of grape dates > blue -previous- years.

Hope it helps. Debugging the formula in the edit formula bar, selecting chunks and pressing F9 might help you understand the intermediate steps. Only remember to undo all the debugging pressing Escape instead of Enter or the debugged portions will be replaced by their values.

Regards!

 

[Cellardoor]

Hello everyone.

So Ram, your question send us to the previous question i've made.

But answering to your questions:

1) and 2) the use of ">=" and the & if we want that our criteria is a cell value and not a Specific value... >=2015 our ">="&B3. - If we write >=B3 we will have an error.

3) - The formula for 2015 has the objective to retrieve the values between 31-12-2014 (">"&D$3) and 31-12-2015 ("<="&E$3)

This is possible because the cells D3 and F3 are dates

I believe this is the point

Regards

 

[Ram Mher]

Hello everyone.

So Ram, your question send us to the previous question i've made.

But answering to your questions:

1) and 2) the use of ">=" and the & if we want that our criteria is a cell value and not a Specific value... >=2015 our ">="&B3. - If we write >=B3 we will have an error.

3) - The formula for 2015 has the objective to retrieve the values between 31-12-2014 (">"&D$3) and 31-12-2015 ("<="&E$3)

This is possible because the cells D3 and F3 are dates

I believe this is the point

Regards

Hi Cellardoor.
Thanks a lot for providing the correct information. Now i have understood that how your formula is working.

 

[juanito]

More on date behaviour.
For the date headers in tables issue, I have overcome this difficulty in some of my models using coercion to force the field into a number (multiplying the header field by 1, for example, like we do with booleans to turn TRUE into 1 and FALSE into 0).
However, when I tried to do this with the file SirJB7 uploaded, it wouldn't work on the middle section (SUMIFS with table, used to illustrate the problem). Looking closer, I saw that my (Spanish) excel no longer recognised these headers as dates, because they were displayed in the original Portuguese instead of Spanish.
I then copied values from the first table, and the dates displayed in Spanish. However, while if I multiply a date by 1 for the first or third table, the formula evaluates to a number, I continue to get a #VALUE! error with the middle table.
This seems very strange indeed to me.

 

[SirJB7]

Hi, Juanito!

My Excel version is in Spanish too. Whenever you use a cell that has a date value as a non local string, you should convert it to a valid date to be recognized by you application. Each local language Excel version handles dates as either numeric values (no matter which format do they have, but what matters is the cell content) or strings locally formatted, not international, not English, not any other one.

So it's always a good practice to build the strings of date as:
=TEXTO(FECHA(2013;12;23);"dd-mmm-yyyy") -----> in english: =TEXT(DATE(2013,12,23),"dd-mmm-yyyy")
and then it'll work everywhere.

Regarding the middle section, that was issue that OP had when trying to use structured tables, it didn't work. Check last section indeed.

Regards!

 

[juanito]

Gracias, Don JB7: estudiaré a fondo sus indicaciones
*** end Spanish version of Chandoo Excel Forum ***
We're sort of way off-thread now, but this does raise interesting points about international versions of Excel, especially for people like ourselves, who toggle between languages.
¡Felices fiestas!

 

[SirJB7]

Hi, juanito!
Same wishes for you, and Feliz Navidad, my Spanish (am I right?) colleague. Will be in touch.
Regards!

 

[SirJB7]

Hi, Juanito!
Just started a conversation with you. Feel free to use the info posted there.
Regards!