please help how to combine IF function with Countif function

[Guest]

Hi,

When I enter the formula which is at the end, I get the result of 3 in Col
B, row 2 instead of 2.


Category New Term
A 2
B
C
D
E
F



New A
New A
New B
Term B
Term E
New F
Term D
Term C
Term C
New F
New B
Term A

{=IF($C$13:$C$24="new",COUNTIF($D$13:$D$24,"A"))}

Thank you for taking time to solve the formula.

Dinesh

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--($C$13:$C$24="new"),--($D$13:$D$24="A"))

Biff

 

[Guest]

thanks. it works.

 

[Guest]

What if I want to add the values which is on Col E,rows 13 to 24. I tried
putting =sum(e13:e24),if(sumproduct(--($C$13:$C$24="new"),--($D$13:$D$24="A"))
It didn't work.

Thanks in advance.

 

[Biff]

Try this:

=SUMPRODUCT(--($C$13:$C$24="new"),--($D$13:$D$24="A"),$E$13:$E$24)

Biff

 

[Guest]

Thanks Biff.

Try this:

=SUMPRODUCT(--($C$13:$C$24="new"),--($D$13:$D$24="A"),$E$13:$E$24)

Biff

 

[Biff]

You're welcome!

Biff

Thanks Biff.