IF & MAX Formula

[KC]

I can't seem to get this formula to work, it gives me a #VALUE. My answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col E has
data for those Creative rows, then place a 1 in the cell, if not place a 0

=IF(A23:A90="Creative",MAX((K23:K90)>0.001,1,0),0) answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)>0.001,1,0),0) answer should = 0
=IF(A23:A90="Creative",MAX((K23:K90)>0.001,1,0),0) answer should = 1
Col A Col E
Creative .25
Creative empty
Creative .25
Advertising empty
Advertising empty
DG .10
DG .10

 

[KC]

Sorry, I meant to say Column K, NOT E - changed it below

 

[shadow4166]

I can't seem to get this formula to work, it gives me a #VALUE.  My answer
should be 1, what i'm asking is, if it says Creative in Col A, and Col E has
data for those Creative rows, then place a 1 in the cell, if not place a 0

=IF(A23:A90="Creative",MAX((K23:K90)>0.001,1,0),0)  answer should = 1
=IF(A23:A90="Advertising",MAX((K23:K90)>0.001,1,0),0)  answer should= 0
=IF(A23:A90="Creative",MAX((K23:K90)>0.001,1,0),0)  answer should = 1
Col A                   Col E
Creative                .25
Creative                empty
Creative                .25
Advertising            empty
Advertising            empty
DG                        .10
DG                        .10

=IF(AND(A23="Creative",E23>0),1,0)

Unless I'm misunderstanding you, which is totally possible. Which
cells will these 1's and 0's be going into?

~Shadow

 

[KC]

What this formula needs to do, is look at the collective list A23 thru A90
and anything that has creative on it, will need to be noted. The way it
needs to be noted though is, if any of the rows that say Creative in them
have a value in column K, then the answer should be 1, however, if theres
nothing in column K, then the answer should be 0. Therefore, the formula
you gave me below wouldn't work because its only looking at cell A23 when it
needs to be looking at A23 thru A90 collectively. I hope I've explained it
so you understand it.

 

[Sandy Mann]

You don't say what you want if there are more than one match. If you want
only 1 returned no matter how many matches there are use:

=IF((A23:A90="Creative")*(K23:K90>0.001),1,0)

This is an array formula which must be entered with Ctrl + Shift + Enter not
just enter.

If you want a count of the matches use:

=SUM(IF((A23:A29="Creative")*(K23:K29>0.001),1,0))

Also array entered, or:

=SUMPRODUCT((A23:A90="Creative")*(K23:K90>0))

Normally entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[KC]

Sandy, thanks for the help. I do want to use the 1st formula you gave me - I
want a 1 returned no matter how many matches there are. However, while this
formula doesn't give me an error, it only gives me a 0 even though there are
amounts in the cells that its pointing to. I made sure to hit CTRL SHIFT
ENTER. Do you have any ideas why it wouldn't show the 1?

 

[Sandy Mann]

Sorry that was my mistake try:

=MAX(IF((A23:A90="Creative")*(K23:K90>0.001),1,0))

Again array entered.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk