sumif help 3

[MaffewW]

Hi pls could someone tell me whats wrong with my simple formula. I want to add up all the values in column C if the date in Column B is January

=SUMIF(B:B,"=Month=1",C:C)

should this not work, pls let me know if I need to provide anymore info

Thanks

Matt

Brighton, UK

 

[carrr]

=SUMIF(B:B,"=January",C:C) shoudl work

Or if 1 = January, then =SUMIF(B:B,"=1",C:C) would work...

...unless I'm missing something...



"'Tis an ill wind that blows no minds." - Malaclypse the Younger

 

[MaffewW]

sorry, the data in column B is a date eg '12-Jan-04' not just the month or a number



Matt

Brighton, UK

 

[dyarwood]

Can you do in column C or any free column

=Month(B2)

Then use that in the sumif. You can hide the column if necessary.

 

[MaffewW]

thanks for the reply, I could do that though I though it would be easy to do this without adding another column, if all else fails thats what I shall do

Matt

Brighton, UK

 

[dyarwood]

Are you familiar with VBA. This can be done with code with no problem. I can't seem to get the SUMIF function to work how you want.

dyarwood

 

[MaffewW]

I'm afraid I'm not. The spreadsheet is updated via DTS from our SQL Server, I'm ok with that bit, just know zilch VBA and a little Excel. If we cant do it with a formula I'll do the option using the month function in another column.

Cheers

Matt

Brighton, UK

 

[dyarwood]

I think the problem facing that formula is that it is only looking at the first date really. But got another idea.

As your data is being updated from your SQL server I would not suggest messing your spreadsheet up with adding columns. You could use a separate worksheet with the formula

=MONTH(Sheet1!A1)

in cell a1 of sheet 2 (date here is in cell a1 of sheet 1)

Then use the sumif equation

=SUMIF(Sheet2!A:A,"=2",Sheet1!B:B)

with the data which needs to be summed in column B

You can then hide the extra worksheet.

This means that all you would see is the data you want.

Does this help in anyway.

dyarwood

 

[bluedragon2]

try:

=SUMPRODUCT((MONTH(B1:B100)=1)*(C1:C100))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[MaffewW]

great, thanks for answers, yours seems to do the trick blue dragon

Matt

Brighton, UK

 

[MaffewW]

actually yours does too dyarwood, and I may use that solution else where on spreadsheet

cheers

Matt

Brighton, UK

 

[dyarwood]

I always forget about the sumproduct function. Good catch blue.