Criteria question

G

Geo

I have the following formula:

=MIN((I18),I16)

How do I add a criteria "Medical" from column "D"
 
P

Peo Sjoblom

What does that mean, which cell holds Medical? Also no need for an extra
parenthesis in your formula

=MIN(I18,I16)



--


Regards,


Peo Sjoblom
 
G

Geo

Thanks
Medical is one of the criterias and is inserted in rows D11 to D5000, in
rows E11 to E5000 is a figure, which the sum total is found in cell I18.
The formula is in cell I20 which sums total for Medical under a figure in
cell I16 (variable fugure).
 
P

Peo Sjoblom

=MIN(IF(D11:D5000="Medical",E11:E5000))

entered with ctrl + shift & enter

if you want to test it against I16 then use

=MIN(MIN(IF(D11:D5000="Medical",E11:E5000)),I16)


also array entered


--


Regards,


Peo Sjoblom
 
P

Peo Sjoblom

Are you saying that you want to SUM E11:E5000 where D11:D500 = Medical but
you only want to add those values less than or equal to I16? Given what you
posted in your first post I would say that was impossible to guess.

=SUMPRODUCT(--(D11:D5000="Medical"),--(E11:E5000<=I16),E11:E5000)

for values less than I16


=SUMPRODUCT(--(D11:D5000="Medical"),--(E11:E5000<I16),E11:E5000)

greater than or equal to I16


=SUMPRODUCT(--(D11:D5000="Medical"),--(E11:E5000>=I16),E11:E5000)


greater than

=SUMPRODUCT(--(D11:D5000="Medical"),--(E11:E5000>I16),E11:E5000)


--


Regards,


Peo Sjoblom
 
Top