Help with 'Sumifs' formula, where I want it to pick up if a cell contains a month (one criteria) and a - figure (in the second criteria)

[Samsung88]

Hi All!

I am new to this forum and I am also rather new at Excel!

I am trying to use a formula that will calculate the below:
If in column A, a date in December appears, tell me the total amount of column C, if the amount is greater than 0

This should then calculate the total expense for December. I then need to calculate the total 'Paid in' - where I would use the same formula, but change the last criteria to IF the amount is less than zero

The table below is the area where my formula will be, and I will be doing this for all months (This formula obviously makes no sense and would not work in excel, but this is just to show the kind of track that I was on)

Any help at all would be greatly appreciated! Thank you all in advance

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Detail[/TD]
[TD] Amount[/TD]
[/TR]
[TR]
[TD="align: right"]01-Dec[/TD]
[TD]Balance brought forward[/TD]
[TD] £ 1,417.00[/TD]
[/TR]
[TR]
[TD="align: right"]06-Dec[/TD]
[TD]Council Tax[/TD]
[TD] £ 47.50[/TD]
[/TR]
[TR]
[TD="align: right"]08-Dec[/TD]
[TD]Direct Debit[/TD]
[TD] £ 62.00[/TD]
[/TR]
[TR]
[TD="align: right"]09-Dec[/TD]
[TD]Water[/TD]
[TD] £ 12.82[/TD]
[/TR]
[TR]
[TD="align: right"]12-Dec[/TD]
[TD]Electricity[/TD]
[TD] £ 13.50[/TD]
[/TR]
[TR]
[TD="align: right"]28-Dec[/TD]
[TD]Gas[/TD]
[TD] £ 15.00[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan[/TD]
[TD]Trip[/TD]
[TD] £ 250.00[/TD]
[/TR]
[TR]
[TD="align: right"]10-Dec[/TD]
[TD]Payment[/TD]
[TD]-£ 944.67[/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan[/TD]
[TD]Essential expense[/TD]
[TD] £ 10.00[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Paid in[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]=sumsifs(A2:A9="*Dec*", "Dec","",C2:C9>0)[/TD]
[TD]=sumsifs(A2:A9="*Dec*", "Dec","",C2:C9<0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Caribeiro77]

Welcome to the board...
How's your date column formatted?
And where do you want the results?
Will you have a different sheet for each month?

 

[Aladin Akyurek]

Are the dates all of the same year, 2014 perhaps?

 

[Samsung88]

Hi Both!

Thank you for responding so quickly,

Caribeiro77
- the date is formatted as "custom" at the minute.
- the answer will be in cell G2 for the balance, and H3 for Paid in
- I was hoping to continue the same table throughout 2015 for all months, and simply adjust the formula in columns G and H, to reflect this

Aladin Akyurek
- December is 2014, where as January is 2015

 

[Aladin Akyurek]

[TABLE="width: 386"]
<TBODY>[TR]
[TD="class: xl64, width: 137, bgcolor: white"]Date
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Detail
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]Amount
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]1-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Balance brought forward
[/TD]
[TD="class: xl66, width: 172, bgcolor: white"]1,417.00
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]6-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Council Tax
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]47.5
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]8-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Direct Debit
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]62
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]9-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Water
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]12.82
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]12-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Electricity
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]13.5
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]28-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Gas
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]15
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]1-Jan-15
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Trip
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]250
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]10-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Payment
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]-944.67
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 137, bgcolor: white"]11-Jan-15
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Essential expense
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]10
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Month/Year
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]Paid in
[/TD]
[TD="class: xl64, width: 172, bgcolor: white"]Balance
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 137, bgcolor: white"]1-Dec-14
[/TD]
[TD="class: xl64, width: 205, bgcolor: white"]1567.82
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]-944.67
[/TD]
[/TR]
</TBODY>[/TABLE]

B12:

=SUMIFS(
  $C$2:$C$10,
  $C$2:$C$10,">0",
  $A$2:$A$10,">="&A12,
  $A$2:$A$10,"<="&EOMONTH(A12,0))

C12:

=SUMIFS(
  $C$2:$C$10,
  $C$2:$C$10,"<0",
  $A$2:$A$10,">="&A12,
  $A$2:$A$10,"<="&EOMONTH(A12,0))

 

[LEXCERM]

How about:
=SUMPRODUCT(($C$2:$C$10>0)*(MONTH($A$2:$A$10)=12)*($C$2:$C$10))
=SUMPRODUCT(($C$2:$C$10<0)*(MONTH($A$2:$A$10)=12)*($C$2:$C$10))

Extra criteria, i.e.:
=SUMPRODUCT(($C$2:$C$10>0)*(MONTH($A$2:$A$10)=12)*(B2:B10="water")*($C$2:$C$10))

 

[Samsung88]

Hi Aladin,

Thank you for the formula there, I have tried it and it worked. I could follow/understand the formula up until the third section, I couldnt figure out what this part was telling Excel to do- though it is obviously very important! Would you be so kind as to explain the below to me, please?


$A$2:$A$10,"<="&EOMONTH(A12,0)</pre>
I like to understand how formulas work, so I can use them again more easily in the future!

Lexcerm
Thank you for your suggestion, I have never used sumproduct before, but I am going to have a play around with the formula now, and see how it works and figure out the best option!
Thanks again

 

[Aladin Akyurek]

Hi Aladin,

Thank you for the formula there, I have tried it and it worked. I could follow/understand the formula up until the third section, I couldnt figure out what this part was telling Excel to do- though it is obviously very important! Would you be so kind as to explain the below to me, please?


$A$2:$A$10,"<="&EOMONTH(A12,0)

</PRE>
I like to understand how formulas work, so I can use them again more easily in the future!

EOMONTH delivers the last day of the month of a given date. When A12 = 1-Dec-14,
EOMONTH will calculate 31-Dec-14.

Lexcerm
Thank you for your suggestion, I have never used sumproduct before, but I am going to have a play around with the formula now, and see how it works and figure out the best option!
Thanks again

[/quote]

SUMIFS is faster than SUMPRODUCT. Moreover, it must be extended with a year test.