SUMIFS and dynamic date ranges

[apgdcg]

Hello,

I am having a hard applying what I've learned about SUMIFS to my data. If column A is mm/dd/yy, and column B is the data I want to sum, can I sum by month, for all the years in my data set? I'd like to be able to have a formula that is dynamic enough to be copied down every row.

Thanks all!

 

[apgdcg]

Farmer Scott,

Thanks, I will look into this. Still a newbie at pivot tables, too!

Alyssa

 

[markmzz]

Hi Alyssa,

Did you try my formula of the post #9?

If yes, what you got?

Markmzz

 

[Joe4]

I took the liberty of "explaining" to myself the purpose of each section of this formula. Do you think this is a correct assessment?

More or less, though the SUMPRODUCT formula probably needs a little more explaining. Let's break it down by the three parts:

--(MONTH(A$2:A$100)=MONTH(A2))
This goes through each row (A2:A100), and checks to see if the Month of that date is equal to the Month in cell A2. If it is, it returns True, else False.

--(YEAR(A$2:A$100)=YEAR(A2))
This goes through each row (A2:A100), and checks to see if the Year of that date is equal to the Year in cell A2. If it is, it returns True, else False.

--(B$2:B$100)
This returns the amount from column B

So, for any row, it goes through all lines of data in our range (rows 2-100), and adds up the values in column B where our first two checks are TRUE.

Does that make sense?

Since the cell references are not absolute, it is "copyable", this is good. However, it doesn't work for my data I replaced the array references with named arrays, and the logical test works, but the whole formula together gives me "#value!"

What am I still doing wrong?

Two possibilities.

Are all of your ranges the exact same size?
Note that the three parts of the SUMPRODUCT all have the same number of rows (99). They have to in order to work together. If one of your named references contains a different number of rows OR columns that another one, it will not work.

Are the dates in column A entered as Dates or Text? They must be entered as Dates.
Here is a simple test to find out. If the following formula for any date cell returns FALSE, it is Text and not a Date.
=ISNUMBER(A2)

If you are still having trouble, post your formula, exactly as you have written it, and also post what each of your named ranges represent (what is the date range that they make up).

 

[apgdcg]

Markmzzz,

It works! Woot woot! But, I'd like to understand why - What is "A$2:A" doing for me?

 

[apgdcg]

Joe4,

I checked, and my date column is entered as a Date (ISNUMBER(a2)=TRUE). All my columns have the same number of rows, and my named ranges are columns within a table, so they change when I add data to the table (right now 1462 rows). Column A is named "Date", and column B is name "Inches".

Here is the formula where A32 = 1/31/1999, A33 = 2/1/1999

=IF(OR(MONTH(A32)<>MONTH(A33),A33=""),SUMPRODUCT(--(MONTH(Date)=MONTH(A32)),--(YEAR(Date)=YEAR(A32)),--(Inches)),"")

Thanks so much for all your assistance - it is much appreciated!

 

[Joe4]

Column A is named "Date", and column B is name "Inches".

I don't think SUMPRODUCT likes you using ENTIRE columns as your ranges.
Try limiting your named ranges to a specific number of rows. It can be some large number that you will never hit.

 

[markmzz]

Markmzzz,

It works! Woot woot! But, I'd like to understand why - What is "A$2:A" doing for me?

Look at the formula in the cell C32:

=IF(OR(A33>EOMONTH(A32,0),A33=""),SUMPRODUCT(--(A$2:A32-DAY(A$2:A32)=A32-DAY(A32)),B$2:B32),"")

The formula look at data until the row of the formula (A$2:A32).

I hope that the information above helps.

Markmzz