How to use SUMIF if range is not consecutive?

[alis748]

Hello excel experts!

I understand the SUMIF function, but how do I SUMIF if the range and sum range is not consecutive in a column (E6:E20) but non-consecutive (E8, E11, E14...)? I'm looking for 2 formulas: SUMIF all unpaid status, and all paid status.

Thanks.

 

[Scott Huish]

Do you mean COUNTIF?

Your other cells shouldn't contain paid or unpaid based on your sample so that shouldn't matter.

For summing the amounts we are looking at the value of 2 cell above:
=SUMIFS(E6:E4999,E8:E5001,"Unpaid")
=SUMIFS(E6:E4999,E8:E5001,"Paid")

for counting them:
=COUNTIF(E:E,"unpaid")
=COUNTIF(E:E,"paid")

 

[alis748]

Thanks! However it's not quite working..? It returns "$0.00" for =SUMIFS(E6:E4999,E8:E5001,"Unpaid") and =SUMIFS(E6:E4999,E8:E5001,"Paid"). I'm not looking to count the "unpaid" and "paid" occurrences.

I'm looking for Total amount for unpaid, which should be $1,231.65+$324 = $1,555.65, and Total amount for paid, which should be $70 + $45 = $115. "Status" is a dropdown with "Paid and Unpaid" choices. I've grouped named "Status" and named all the amounts (E6+E9+E12...) as "PaymentAmount". I tried =SUMIF(Status, "Unpaid", PaymentAmount) and that also returned "$0.00". Any idea how to fix it?

Thanks!

 

[kevin9999]

You can usually get this by offsetting the criteria & values range, for example (note Sumif not Sumifs)

Book1
	E	F	G	H
5
6	$1,231.65		Paid:	$115.00
7	xyz		Unpaid:	$1,555.65
8	Unpaid
9	$324.00
10	xyz
11	Unpaid
12	$70.00
13	xyz
14	Paid
15	$45.00
16	xyz
17	paid
18
Sheet1

Cell Formulas
Range	Formula
H6	H6	=SUMIF($E$8:$E$5001,"Paid",$E$6:$E$4999)
H7	H7	=SUMIF($E$8:$E$5001,"Unpaid",$E$6:$E$4999)

 

[Alex Blakenburg]

If neither Kevin's or Scott's formulas work then it is likely you have a data issue.
If you click on one of the amounts and change the number format does what you see change ?
Alternatively if you control click 2 amounts does it show the sum in your status bar ?

If not try these:
(you can use "--" instead of "VALUE" if you prefer)

=SUM(FILTER(VALUE($E$6:$E$4999),$E$8:$E$5001="Paid",0))

=SUM(FILTER(VALUE($E$6:$E$4999),$E$8:$E$5001="Unpaid",0))

 

[alis748]

Yes guys, it worked! Thank you so much! I have definitely learnt something new.

 

[Alex Blakenburg]

Thanks for the feedback. I assume you got Scott's SumIfs and Kevin's SumIf to work. Would you mind confirming that is the case and perhaps marking one of those posts as the solution so that it might help others ?

 

[alis748]

Absolutely! Sorry I'm new to this very useful forum!

I used kevin9999's solution and just changed the end of the range to 65 instead of 5001 as that was the last cell of my table Worked perfect!

 

[kevin9999]

Absolutely! Sorry I'm new to this very useful forum!

I used kevin9999's solution and just changed the end of the range to 65 instead of 5001 as that was the last cell of my table Worked perfect!

Glad we were able to help, and welcome to the forum!