CODEWITHSUNDEEP
excelpivot-table
Alistair Weir
Alistair Weir

Reputation: 1849

CountIF over a criteria range

I have the following columns of data:

Pool Amount|Calculated Payment| 10% | 15% | 20% | 30% | 40% | 50%

Pool Amount and Calculated Payment are static currency fields, the percentage fields all contain variations on this formula:

e.g. C2 =IF(B2<(A2*0.9),A2*0.9,IF(B2>(A2*1.1),A2*1.1,B2))

The formula caps the calculated payment at the thresholds determined by the relevant % in relation to the Pool Amount. What I want to do is a count for each % field showing the number at the minimum and maximum.

For one cell in the 10% field I would use:

Min =COUNTIF(C2,A2*0.9) and Max =COUNTIF(C2,A2*1.1)

I could then produce two columns of 0s and 1s and SUM them but it seems a bit clunky. Is there a variation on the following formula that I could use:

=COUNTIF(C2:C1162, A2:A1162*0.9) to show a total count in one cell?

Is this potentially a job for a pivot table with a custom function?

Upvotes: 0

Views: 6611

Answers (2)

lc.
lc.

Reputation: 116458

I believe you can accomplish what you want with an array formula. Your formula for the "minimum 10% cell" should be correct, just press CTRL+SHIFT+ENTER when you enter the formula to make it an array formula.

I believe you need to use SUM instead of COUNTIF in the array formula (to sum all TRUEs as 1 and FALSEs as 0). It has been a while since I've used one.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46341

COUNTIF can't be used to compare each element of one range/array against each element of another but you can use SUMPRODUCT to do that and avoid formulas that require "array entry", i.e. this formula to count the Mins in the 10% field

=SUMPRODUCT((B2:B1162< A2:A1162*0.9)+0)

and for Maxs

=SUMPRODUCT((B2:B1162> A2:A1162*1.1)+0)

Those formulas just use the condition from your original formula applied to ranges rather than single cells. SUM can be used in place of SUMPRODUCT but then you do need CTRL+SHIFT+ENTER

Upvotes: 1

Related Questions