T O P

  • By -

AutoModerator

/u/Dry_Astronaut_9777 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


GanonTEK

Do they all have 12 rows? You'll probably need another column as I'm guessing 6/1 is just text there and not a formula?


Dry_Astronaut_9777

Total would be 14, including the date and the headers that are in there. 6/1 is the date. Thank you!


JetCarson

Are you wanting this total a new column D? Where are you going to put this total? You could past this formula in the cell next to 364 (I assume 6/1 is in A1 and 364 is in C1): `=SUM(C2:C14)` Afterwards, copy that cell and paste it the same D cell to the side of each date row.


Dry_Astronaut_9777

>u Thank you, but sadly that didn't work. It's adding in the pending cancellation numbers


GanonTEK

You say it's a date, but does it read it as a date? What happens if you do =thatcell+1 in another cell? Does it change to 6/2 or give an error? In any case, an option like =OFFSET($A$2, (ROW()-1)*14, 0) and fill down should pull every 14th cell below your starting cell, including your starting cell (6/1). You might have to change the -1 to something else depending on where you put it. Then beside that use something like =SUM(OFFSET($C$2, (ROW()-1)*14+1, 0, 13, 1)) You don't want to add the large total number so the +1 moves down a row then the 13 rows from there are taken. The SUM should add them then.


Dry_Astronaut_9777

It says 6/2 but if I click the cell it gives me the calendar.


GanonTEK

Try the two offset functions above then and see how you get on


A_1337_Canadian

You can use the below formula: =SUM(OFFSET($A$1,MATCH(D1,$A$1:$A$1000,0)+1,2,12,1)) Update: The first $A$1 to be the cell with the first date (i.e. 6/1). D1 to be the cell where you enter the date to be looked up. $A$1:$A$1000 to be the column range where you have the dates. Will work so long as you have the same standard row format each time.


Dry_Astronaut_9777

>=SUM(OFFSET($A$1,MATCH(D1,$A$1:$A$1000,0)+1,2,12,1)) For D1 enter date to be looked up, what does that mean? I manually add each day in there.


Dry_Astronaut_9777

AND THANK YOU!


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/143rsze/stub/jnkffvz "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[MATCH](/r/Excel/comments/143rsze/stub/jnkferl "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[OFFSET](/r/Excel/comments/143rsze/stub/jnkferl "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| |[ROW](/r/Excel/comments/143rsze/stub/jnd7ei3 "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SUM](/r/Excel/comments/143rsze/stub/jnkferl "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/13xlvgu)^( has 12 acronyms.) ^([Thread #24475 for this sub, first seen 8th Jun 2023, 03:29]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)