SOLVED

Excel

Occasional Contributor

I have an energy worksheet and I am trying to sum depending on month and current year if for example I trying to total  cells that apply to Electric

any suggestions

4 Replies

@MarkBeck54 

One option would be to create a pivottable based on the data and filter it on Electric.

Another option is to use SUMIFS.

Let's say the date is in A2:A100, the category such as Electric in B2:B100 and the amount you want to sum in C2:C100.

The total for the current month is returned by

 

=SUMIFS($C$2:$C$100, $A$2:$A$100, ">"&EOMONTH(TODAY(), -1), $A$2:$A$100, "<="&EOMONTH(TODAY(), 0), $B$2:$B$100, "Electric")

@Hans Vogelaar Below is a screeshot of what I am trying to achieve J2 & J3 is where I want the results

A11 onwards is a list of dates -C11 Onwards is inputed values D11 is droplist E is function if <= K7 

I am trying to sum month in in I2(at the moment this value of K7).

So I need to use I2 as criteria as well as Year and in this example sum of april/2022 to be shown  in K2

I havent tried your solution yet so dont know if it works I have tried countif as in function bar   and does pull out the numer of times.

Thanks

best response confirmed by MarkBeck54 (Occasional Contributor)
Solution

@MarkBeck54 

Enter the following formula in K2

 

=SUMIFS($C$11:$C$74, $A$11:$A$74, ">"&EOMONTH($K$7, -1), $A$11:$A$74, "<="&EOMONTH($K$7, 0), $D$11:$D$74, J2)

 

Fill down to K3.

Thank you works a treat