# How can i get sum of data with specific date within table not just a column?

I'm need help to make formula. As the title said i need to get sum of data with specific date within table not just a column. Is that possible?

Here is the dataset below:

I tried to make some of formula but the closest one is

``````=ARRAYFORMULA(SUM(IF(EOMONTH(J2;1)<=A2:H5>=DATE(YEAR(J2);MONTH(J2);1);INDIRECT(ADDRESS(ROW(A2:H5);COLUMN(A2:H5)+1));0)))

J2 = 01/10/2023 #My time format is dd/mm/yyyy
``````

But the result is 3.200.000 instead of 2.000.000

The thing i want to achieve is i want to get summarize of the entire table by specific month e.g i want to get the total ammount of October.

This is just a sample set. The real sheet contain hundreds of row and dozens of column. I know i can achieve it with sumif on each column but i can't do that with this one. Any help and suggestion are appreciated. Thanks before.

• Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Oct 3, 2023 at 12:19

Transform the data to a two-column format and use the `sum(filter())` pattern, like this:

``````=let(
data; wraprows(tocol(A2:H); 2);
dates; choosecols(data; 1);
amounts; choosecols(data; 2);
sum(filter(amounts; text(dates; "yyyy-MM") = text(J2; "yyyy-MM")))
)
``````

See let(), wraprows(), choosecols(), and filter().

# Formula

``````=ARRAYFORMULA(
SUM(
IF(ISDATE_STRICT(A2:H5)*
(J2-DAY(J2)=INT(A2:H5)-DAY(A2:H5));
OFFSET(A2:H5;0;1);)))
``````

The same formula using LET to simplify future edits:

``````=LET(rng;A2:H5; ARRAYFORMULA(
SUM(
IF(ISDATE_STRICT(rng)*
(J2-DAY(J2)=INT(rng)-DAY(rng));
OFFSET(rng;0;1);)))
``````

If the dates you are checking `J2-DAY(J2)` will always be larger than the value in the amounts columns, then the ISDATE_STRICT() condition can be left out. For example, if the earliest date you will check is in `December 1, 2022` and your amounts are less than `44895` (equivalent to Nov. 30, 2022) then there is no need to test the dates, and the following shorter formula can be used:

##### [ Optional ]  Skip Date Checking
``````=ARRAYFORMULA(
SUM(
IF(J2-DAY(J2)=INT(A2:H5)-DAY(A2:H5);
OFFSET(A2:H5;0;1);)))
``````

The same formula using LET to simplify future edits:

``````=LET(rng;A2:H5; ARRAYFORMULA(
SUM(
IF(J2-DAY(J2)=INT(rng)-DAY(rng);
OFFSET(rng;0;1);)))
``````

## Calculation Approach

1. Since dates are numbers, to compare dates without their DAY component, you can simply subtract it from each date prior to the comparison using `=date-DAY(date)`. [ See table below ]
2. If the (modified) date matches (modified) `J2` then return the value in the column that is OFFSET one column to the right of the current date being checked, otherwise return nothing.
3. SUM any values returned

### Example: Comparing Dates without Days

date date (#) DAY(date) date-DAY(date) # date-DAY(date) * [see note below]
`1/10/2023`   `J2` `45200` `1` `45199` `30/09/2023`
15/10/2023 45214 15 `45199` `30/09/2023`
21/10/2023 45220 21 `45199` `30/09/2023`
01/11/2023 45231 01 45230 31/10/2023

* Note: Since there's no day `0` in any month, modified dates are the last day of the previous month. This is consistent for all dates compared and is just being noted for informational purposes in case you adapt this formula in the future.

## Assumptions

1. Spreadsheet location settings are not US-style:
• They use a comma `,` as their decimal separator.
US-style uses a period `.`
• They use a semi-colon `;` to separate arguments in formulas.
US uses a comma `,`
• They use a back-slash `\` to separate columns in arrays.
US-style uses a semi-colon `;`
• Anyone using US-style location settings (most non-European countries including US, UK, Canada, Australia) should update the formulas in this answer accordingly.
2. Dates
• If using the formula that includes `ISDATE_STRICT`, then dates must be numbers formatted in any date format (including custom) as long as the format doesn't display time: for example, the custom format "dd" will still work as long as it's a date format, not a text string.
• If not testing dates with `ISDATE_STRICT` then they need simply be valid numbers and the format doesn't matter.
• If there definitely (even accidentally) won't be any underlying times in any of the dates (even if hidden) then the INT function can be removed from the formula. e.g. `INT(A2:H5)` Its sole purpose is to discard any times from the dates whether displayed or not.
``````   # Date1            # Date2
1/1/2023 10:04:48  1/1/2023 04:04:48

=INT(Date1)        =INT(Date2)
=INT(44927,42)     =INT(44927,17)
=44927             =44927
``````