Excel Formula For Calculating If Two Cells Equal Criteria Then Sum The Cells Next To The Line

1
votes

I'm currently trying to create a formula that will look at two columns for a criteria and then sum up the cells next to it, For example,

Column A    Column B    Column C
Date Range  Type        Value
01/01/2018  Credit      £4.00 
02/01/2018  Debit       £4.00
05/01/2018  Credit      £6.00
01/02/2018  Debit       £5.00 
01/03/2018  Credit      £6.00

What i want the formula to do is look at column A for if it is greater or equal to the current month and column B equals credit, then calculate and add the cells together, so for the above example the answer would be the credit for the month,

So for the month of January the answer would be £10

So far i have tried:

=SUM(IF((W7:W28<=X1)+(X7:X28=AA2),Y7:Y28,0))

But this just does the SUM for all cells in the array

Tried:

=SUMPRODUCT(W7:W28>=X1 * X7:X28=AA2 *Y7:Y28)

But this just gives me a VALUE error unfortunatly :(

Can you please advise how i can create a formula to do a SUMIFS but with an array and equal to this month

1
excel-formula
Can you clarify your date format? Is it MM/DD/YYYY or DD/MM/YYYY? – Forty3
Date/Month/Year - DD/MM/YYYY – Luke Tromans

1 Answers

0
votes

Try the following for a January SUM:

=SUMIFS(C2:C6,A2:A6,">="&"01-Jan-2016",A2:A6,"<"&"01-Feb-2018",B2:B6,"=Credit")

The SUMIFS function is of the format: 

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

So we tell the system to sum across C2:C6 but only when

  • A2:A6,">=" & "01-Jan-2018" : the value in the A column is greater than 01-Jan
  • A2:A6,"<" & "01-Feb-2018" : the value in the A column is less than 01-Feb
  • B2:B6,"=Credit" : the value in the B column equals the string "Credit"

Hope this helps.