how to make an IF function that has the same result as COUNTIF?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex Khan - 24 Jul 2008 21:29 GMT

I imported a set of Color Functions from http://cpearson.com/excel/colors.aspx

And I want to replace where I added COUNTIF in my sheet with a new function
that will count in the specified range, using the specified criteria and if
the color is red.

So I thought that making an IF function that uses COUNT (replaced by
CountColor to make it look for red cells) would do the trick. I kindof got
stuck so any help is appreciated.

I'd also like to modify this formula:

=SUMPRODUCT(--('Intarzieri (2)'!B12:B1050=C4),('Intarzieri (2)'!D12:D1050))

so that it would add only the red celled ones.

Reply to this Message

Peo Sjoblom - 24 Jul 2008 21:41 GMT

If you go back to Chip's page you will see he has an example with an array
formula
with red fill color

Signature

Regards,

Peo Sjoblom

>I imported a set of Color Functions from
>http://cpearson.com/excel/colors.aspx
[quoted text clipped - 15 lines]
>
> so that it would add only the red celled ones.

Reply to this Message

Alex Khan - 24 Jul 2008 22:30 GMT

As far as I can tell you mean this formula:

=SUM(B11:B17*(COLORINDEXOFRANGE(B11:B17,FALSE,1)=3))

However, this will add up all the values, I'd like to add only the ones with
a specific criteria.

I have the following table (I've removed the column with each date):

Name Date
Name1 1/06/06
Name1 1/05/06
Name2 1/06/06
Name3 1/06/06
Name2 1/05/06

So,
I'm using COUNTIF(A1:A5,"Name 1") so that it will show me now many absences
does Name1 have (2).
However, if that absence was unmotivated I want to highlight it in red so
when I add up all the absences I want to see only the unmotivated ones.

> If you go back to Chip's page you will see he has an example with an array
> formula
[quoted text clipped - 19 lines]
> >
> > so that it would add only the red celled ones.

Reply to this Message

Peo Sjoblom - 24 Jul 2008 22:53 GMT

Forget about COUNTIF, add the criteria to the example formula

=SUMPRODUCT(--(COLORINDEXOFRANGE(B11:B17,FALSE,1)=3),--(B11:B17="x"))

will count red x in B11:B17

Signature

Regards,

Peo Sjoblom

> As far as I can tell you mean this formula:
>
[quoted text clipped - 46 lines]
>> >
>> > so that it would add only the red celled ones.

Reply to this Message

Alex Khan - 24 Jul 2008 23:15 GMT

Thanks a lot. That's what I wanted to do in the first place, forget about
COUNTIF :)

Your tip did the trick

Reply to this Message