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

Date	Detail	Amount
01-Dec	Balance brought forward	£ 1,417.00
06-Dec	Council Tax	£ 47.50
08-Dec	Direct Debit	£ 62.00
09-Dec	Water	£ 12.82
12-Dec	Electricity	£ 13.50
28-Dec	Gas	£ 15.00
01-Jan	Trip	£ 250.00
10-Dec	Payment	-£ 944.67
11-Jan	Essential expense	£ 10.00

<tbody>
</tbody>

Paid in	Balance
=sumsifs(A2:A9="*Dec*", "Dec","",C2:C9>0)	=sumsifs(A2:A9="*Dec*", "Dec","",C2:C9<0)

<tbody>
</tbody>

 

[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]

Date	Detail	Amount
1-Dec-14	Balance brought forward	1,417.00
6-Dec-14	Council Tax	47.5
8-Dec-14	Direct Debit	62
9-Dec-14	Water	12.82
12-Dec-14	Electricity	13.5
28-Dec-14	Gas	15
1-Jan-15	Trip	250
10-Dec-14	Payment	-944.67
11-Jan-15	Essential expense	10
Month/Year	Paid in	Balance
1-Dec-14	1567.82	-944.67

<TBODY>
</TBODY>

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.