CODEWITHSUNDEEP
excelexcel-formulasum
SumeetN
SumeetN

Reputation: 128

To sum SPECIFIC number of cell values based on criteria

I have a list

enter image description here

How to calculate the sum of "amount" from Top 5 blank(unpaid) cells? In this case, it would be 13,5,13,11,4. I don't want to change cell range every time Blank cell are changed to Paid. My real problem is very different than this, but if this has a solution, I can probably work on that.

Upvotes: 2

Views: 1027

Answers (1)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

You will need a couple of "Helper" columns:

  • an ISBLANK() formula testing column B
  • a COUNTIF() where column C is TRUE from the top of column C down to the adjacent column C cell.

Assuming Amount in column A and Paid/Unpaid in column B, starting from row 4 then:

  • in C4, =COUNTIF($C$4:C4,TRUE) and copied down to last row
  • in D4, =SUMPRODUCT(($C$4:C4)*($A$4:A4)*(($D$4:D4)<NumberToSum+1)) also copied down.

Added bonus: 'NumberToSum' is a named range in F1 where you can enter a number.

Image below may help to illustrate Sumproduct with variable range and user criteria

Note that for completeness, E3 should have a formula '="Sum of 1st " & NumberToSum &" Unpaid"' to display the number being summed.

Upvotes: 1

Related Questions