CODEWITHSUNDEEP
excelexcel-formula
renar23
renar23

Reputation: 3

Excel IF formula for multiple conditions (less than and greater than)

I have a table where one column is for salaries of employees. There are two other columns next to it, one with the minimum salary and the other with the median salary. I would like to write a formula that in one cell would count the number of employees that have their salary greater than the minimum but less than the median.

I have tried various approaches, including things like =SUM(IFS(D28:D48<F28:F48,1, D28:D48>E28:E48,1))

So, in essence, there should be one cell that shows the count where amounts in the salary column are larger than the amounts in minimum column but smaller than amounts in median column.

Upvotes: 0

Views: 355

Answers (2)

Black cat
Black cat

Reputation: 6281

Try this:

=COUNTIFS(D1:D7,">"&E1,D1:D7,"<"&F1)

enter image description here

Adjust to your data

=COUNTIFS(D28:D48;">"&E28;D28:D48;"<"&F28)

With different limits in rows: (array formula)

=SUM((D$2:D$6>E$2:E$6)*(D$2:D$6<F$2:F$6))

enter image description here

Upvotes: 0

L42
L42

Reputation: 19737

Try this:

=SUMPRODUCT(--(D28:D48>E28:E48),--(D28:D48<=F28:F48))

Upvotes: 0

Related Questions