COUNTIFS Multiple Criteria

[lpost1214]

Hello,

I am trying to write a COUNTIFS formula to include a criteria that will subtract a value in column P from the row below in column I and if the result is less than 0 then it should be counted, but I can't seem to write this correct so that excel will accept this. Any help is appreciated. Thanks!

IF(B4="","",COUNTIFS($B$4:B4,B4,$O$4:O4,"=0",$P3-$I4, "< 0")*(B4=B4))
[TABLE="width: 65"]
<tbody>[TR]
[TD][TABLE="width: 49"]
<colgroup><col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <tbody>[TR]
[TD="class: xl65, width: 65, bgcolor: transparent"][TABLE="width: 65"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[DRSteele]

That P3toI4 operation you're trying to include is a range of the wrong size. Excel's Help feature for function COUNTIFS declares, "Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other."

You probable need an array formula to complete this task. If you provide some sample data together with expected outcome we can conduct experiments.
​