SUMFIS btwn dates

I am scratching my head because this is a simple formula I have used in the past, but I cannot figure out why this is returning 0 when there are 2 values that should be summed in this period:

=SUMIFS(Amount:Amount, Date:Date, <=DATE(2024, 6, 30), Date:Date, >DATE(2024, 3, 31))

Find more posts tagged with

Accepted answers

SSFeatures

If you right click on the Date column and go to "Edit Column Properties", what is the Column Type? Is it Date or is it Text/Number?

All comments

SSFeatures

Hi @Btrombler ,

I just made a sheet then tested out your formula and it works for me:

Is this the real formula that you use in your sheet, or does your real formula use cross-sheet references or something? If they do use cross-sheet references, make sure that your reference includes the entire column.

Btrombler

hi @SSFeatures yes, that is the exact formula. Of note, this is in a sheet summary, not an actual cell.

SSFeatures

Ah good point. I just tried it as a sheet summary and it looks correct to me.

Can you try recreating the field and see if that fixes the formula? Super weird!

Btrombler

@SSFeatures gave that a try, but it didnt seem to help :(

=SUMIFS(Amount:Amount, Date:Date, <=DATE(2024, 6, 30), Date:Date, >DATE(2024, 3, 31))

SSFeatures

If you put the formula in a cell within the sheet, does it work?

What if you try duplicate the Date column (e.g. Date2), then update the formula to use Date2? Similarly with Amount and "Amount2"?

I'm just curious if something is buggy in your sheet somehow.

Btrombler

In the cell w/n the sheet, it still brings up "0"

I tried with both the updated columns and a mix of one and not the other and they all turn up 0 :(

=SUMIFS([Amount2]:[Amount2], [Date2]:[Date2], <=DATE(2024, 6, 30), [Date2]:[Date2], >DATE(2024, 3, 31))

Btrombler

@SSFeatures

Btrombler

just tried creating a WHOLE new sheet and c/p the data over and it's still turning up 0

@SSFeatures

=SUMIFS(Amount:Amount, Date:Date, <=DATE(2024, 6, 30), Date:Date, >DATE(2024, 3, 31))

SSFeatures

If you right click on the Date column and go to "Edit Column Properties", what is the Column Type? Is it Date or is it Text/Number?

Btrombler

@SSFeatures omg OF COURSE! that did it!! thank you so much. Can't believe i didnt check that piece.

SSFeatures

Haha no problem! I should have thought of that way earlier too. Took us both a long time for something simple lol. Glad it works now!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.