Add totals of all rows matching specific month

[mrcois]

I want to add the totals of all the rows of a specific month.

Please attached document with example and requirement in cell D9 and E9 of the first tab?

[ben_hensel]

In D9:

Please Login or Register  to view this content.

And pull down.
You can change the reference in the first one from "Private" to "Public" for the code in E9.

Note that your Date values in Table1 are a mix of actual dates and text strings -- you will have to clean up that range so they are all date values before the formula can deliver as expected.

[JohnTopley]

Try

=SUMIFS(Travel_Logbook_2017_A!$L:$L,Travel_Logbook_2017_A!$A:$A,">="&Travel_Logbook_2017_Aa!$C9,Travel_Logbook_2017_A!$A:$A,"<="&EOMONTH(Travel_Logbook_2017_Aa!$C9,0))


=SUMIFS(Travel_Logbook_2017_A!$M:$M,Travel_Logbook_2017_A!$A:$A,">="&Travel_Logbook_2017_Aa!$C9,Travel_Logbook_2017_A!$A:$A,"<="&EOMONTH(Travel_Logbook_2017_Aa!$C9,0))


PLEASE ensure all your dates are EXCEL dates: many are not formatted correctly.

[johnmpl]

Hi, to all!

You have dates like text in A column. You have to fix them for good purposes. Also you have two dates (I put in red font) that are in 2017 (not added in resume).

The formula purpose is:
[D9] : =SUMIFS(Travel_Logbook_2017_A!L:L,Travel_Logbook_2017_A!$A:$A,">="&$C9,Travel_Logbook_2017_A!$A:$A,"<="&EOMONTH($C9,0))

Drag it down and right.

Also, you can do it with Pivot Table.

Check file with results. Blessings!

[mrcois]

What is the quickest way to sort out the formatting, instead of going through each row?

[ben_hensel]

Fortunately the text appears to all be in the same format, so you can create a helper row with this formula:

Please Login or Register  to view this content.

Pull down to fill, then copypaste-values over the top of the original data, then delete the helper row, and you're done.

[mrcois]

THANKS Ben. All work and good...