CODEWITHSUNDEEP
databaseexcelexcel-formulaconditional-statements
PavanV
PavanV

Reputation: 25

How to get data fulfilling two conditions in excel?

I am currently working on an excel data sheet exampled as below.My aim is to get the cell value which fulfills the two conditions.

.........A............B...............C................D

======= ======= ======= =======

1.......ID......sales......Response

2...... N01.... 122....... Positive

3...... N02.... 151....... Positive

4...... N03.... 123....... Negative

5...... N04.... 124....... Positive

6...... N05.... 154....... Negative

7...... N06.... 162....... Negative

8...... N07.... 124....... Positive

9...... N08.... 108....... Positive

10.....N09.... 137........ Negative

11.....N10.... 109........ Negative

Now I want the Maximum Sales Value whose Response is Positive. So here the First condition is I want Maximum sales value and second is The Response is Positive. I want such record using formulas only.So please help if you know.

Upvotes: 0

Views: 96

Answers (2)

Tim Edwards
Tim Edwards

Reputation: 1028

Or

=INDIRECT("B"&MATCH(MAX(IF(C2:C11="Positive",D2:D11)),D2:D11))

(also array formula) I would have commented on the other answer but don't have enough points yet...

Upvotes: 0

Mrig
Mrig

Reputation: 11702

Try:

=MAX(IF(C2:C11="Positive",B2:B11))

this is an array formula so commit it by pressing Ctrl+Shift+Enter.

See image for reference:

enter image description here

Upvotes: 1

Related Questions