SUMIFS, where argument date is splitted into days along a column, months along a row.

Question

SUMIFS, where argument date is splitted into days along a column, months along a row.

Hi all,

I'm facing following problem.

A table containing numbers according to days and months such as the one here :

The table is organized as such and I can't change the layout since it is shared and readable that way.

For example, i can't set it up as a long table of 365 rows with the actual date in each row.

So days must be read along Col A and months Row 1.

My issue :

Between Today and 31 of december, the content of each cell minus 9 must be added considering that:

-non pertinent cells are empty (ex : April 31st, February 29th, ..)

-vaccation period cells are also empty.

otherwise 0.

For example the value in C2 to be added, if Today was before Feb 1st, and out of vaccation period, is : 9 - 1 = 8

And so on.

I tried SUMIFS, but the date definition with days in column and month in row doesn't seem to work in that function. Well, I couldn't make it...

If someone has an idea of counting without changing the layout, that would be great.

Thanks all for your time and help.

Lionnel.

Posted on Oct 2, 2019 6:34 AM

Reply

Answer 1

Top-ranking reply

t quinn

Level 5

7,056 points

Oct 2, 2019 10:50 AM in response to lionnelfromparis

Hi Lionnel,

OK, that is clear. the context was important.

I am going to give this solution in 2 pieces.

This will give us the bookings from today till 12/31. OFFSET can be tricky, it can screw up if extra headers are added.

Rows 33 and 34 are footers.

K33=IF(MONTH(TODAY())=MONTH(K$1),SUM(OFFSET($A$1,DAY(TODAY())−1,MONTH(TODAY()),31,1)),SUM(K))

This sums everthing in the current month starting with today's date. Other months are simply totaled.

K34=(COUNT(K)×9)−K$33

quinn

ps congrats on sept.

Reply

Answer 2

Oct 2, 2019 8:40 AM in response to t quinn

Ok, I tried to make sketch of my table here.

Normally there is a number in each cell between 0 and 9 reflecting bookings confirmed (before Today) or changing (after Today). I did not fill all the cells. 0 means I have 9 rooms to rent, 9 means I'm full.

It changes everyday as long as it is ahead of Today. Once the day has passed the number in that cell is frozen.

There is of course no booking during vaccation.

In this period between Today and dec 31st, I want to check if I still have rooms to rent or if bookings in cells are all at 9. It is a kind of trend i check out everyday.

In the pdf here I focused on the period between today and end of year to show what I'd like.

Hope it gets better this time...🙂

Lionnel.

Reply

Answer 3

t quinn

Level 5

7,056 points

Oct 2, 2019 7:42 AM in response to lionnelfromparis

Hi lionnel,

First, you are pushing Numbers in a way it is not designed to work. This could be fun.

Second, I really don't understand your conditions.

Are you saying that in the cell for October 2 (Today) each of the cells from previous dates will be added (after subtracting 9 if they are not 0)? That seems to be what your first paragraph is saying. Your next paragraph seems to contradict this. If Today is after Feb 1 what is the needed value in C2? Your example subtracts current value from 9.

As a practical example, what is your looked for value in August 1? Was it different when the date was still in July? Please show your work.

quinn

Reply

Answer 4

Oct 2, 2019 7:49 AM in response to t quinn

Hello Quinn,

Thanks for replying so fast.

Yes I know this is not the way Numbers should be used, but some colleagues a nice condensed view of bookings alog the months. And I'm afraid I can't turn them to my opinion.

The idea is :

each cell that is Today or after Today, do (9 - cell content) , and add it until Dec 31st and removing vaccation days as well if it happens to be in the range.

Is it better now ?

Lionnel.

Reply

Answer 5

t quinn

Level 5

7,056 points

Oct 2, 2019 8:06 AM in response to lionnelfromparis

Hi Lionnel,

Not really. You are only describing half of the situation. What you are saying is Oct 2 having no entered value is worth 9. What was it worth yesterday? Looks like your formula could count the days till Dec 31 and multiply by 9. Don't see any vacations days between now and then, don't see any values in the cells.

All the vacation days (assume April-Aug) are past so they are irrelevant.

This is why I want the value to be used in Aug 1. Or the value to be used for Mar 31. Remember you have given no context for these operations, it is just flipping numbers in random ways from this end.

quinn

Reply

Answer 6

Oct 3, 2019 4:36 AM in response to t quinn

Hi Quinn,

It took me a while to analyse your solution and understand what OFFSET is doing and how it works.

Now I'm fine.

Smashing !.

I just adapted it to my needs.

Thanks a lot.

Lionnel.

Reply

Answer 7

t quinn

Level 5

7,056 points

Oct 3, 2019 6:43 AM in response to lionnelfromparis

Hi Lionnel,

Glad that works for you. OFFSET can be a powerful tool and I am glad it is available.

quinn

Reply

SUMIFS, where argument date is splitted into days along a column, months along a row.

Similar questions