sumif, function picking wrong value

[nicci113]

Hi There,

I'm doing sumif and its not working correctly and need some help! The sumif is only picking first value. I want the cell where it says Date to be dynamic

[Pete_UK]

You would normally use SUMIF like this:

=SUMIF(criteria_range,criteria,sum_range)

but for your criteria_range you only have one cell (i.e. A1), thus you will only get one value from your sum_range (i.e. from A2). You would normally have dates in one column and then have the corresponding values in an adjacent column.

Please explain more clearly what you want to achieve.

Hope this helps.

Pete

[Speshul]

The RANGE and the SUM Range need to be the same size. You are only getting one result, because the RANGE is only one cell in size.

Also the syntax is not correct - what are you trying to achieve?

[nicci113]

@Speshul
I figured out that part coz, if i add the similar range it works, but its not a clean way to write!

[Pete_UK]

Attach another sample file which shows more dates - do you have different monthly values in different columns?

Pete

[nicci113]

Thanks for the assistance!

All I want to do is result in D2 using sum if and the reason to do that is I've data going back 12 months and need the caculcation based on cell E1. Data is in similar format as posted just extra columns.

I hope it clear up a wee bit!

[AlanY]

may be this is what you wanted

[newdoverman]

Observation: You are comparing A1 and E1. A1 looks like a date but is only text that looks like a date while E1 is a real date. Change A1 to be a real date. Then the following will work.

=SUM(IF(A1=E1,A2:A14))

[nicci113]

Please see attached with more datasets. I need sum total based on the month in E1..

[nicci113]

@AlanY I think you reply gives me the answer but honestly I don't understand the logic!

[AlanY]

@AlanY I think you reply gives me the answer but honestly I don't understand the logic!

It's just match the date on E1 to A1 to D1 then add the column the matched all the way down,
Both the range A1 to D1 and A2 to A50 can be amend to suit

[Pete_UK]

Based on your attachment in Post #9, you can use this formula in E2:

=SUM(INDEX(A2:C14,,MATCH(E1,A1:C1,0)))

which is non-volatile, but you have to put the date 01/07/2014 in cell A1 instead of the text value "Jul-2014" (copy the format from B1 to A1 using the Format Painter).

Hope this helps.

Pete

[newdoverman]

Using your supplied data, this works

=SUM(OFFSET(A1,1,MATCH(E1,A1:C1,0)-1,100,1))