Item Count

[RAMDAS]

Can someone please help me with this query
Questions
1.Count of All "RESLOVED" or "CLOSED" item from column B for 'A' item from column A
2.Count of All "RESLOVED" or "CLOSED" item from column B for 'B' item from column A
3.Count of All "RESLOVED" or "CLOSED" item from column B for 'C' item from column A
--------------------------------------------------------------------------
A B
----------------------------
A RESOLVED
A ASSIGNED
A OPEN
A CLOSE
A RESOLVED
A RESOLVED
B RESOLVED
B RESOLVED
B RESOLVED
B RESOLVED
B RESOLVED
B CLOSE
B CLOSE
B CLOSE
B CLOSE
B CLOSE
B CLOSE
B CLOSE
C CLOSE
C OPEN
C OPEN
C OPEN
C OPEN


Thanks in Advance
~ram

[Paul]

1.Count of All "RESLOVED" or "CLOSED" item from column B for 'A' item from column A
2.Count of All "RESLOVED" or "CLOSED" item from column B for 'B' item from column A
3.Count of All "RESLOVED" or "CLOSED" item from column B for 'C' item from column A

For all three equations, use this formula, but change the ="A" to ="B" and ="C" for #2 and #3:

=SUMPRODUCT(--($A$1:$A$40="A")*(--($B$1:$B$40="RESOLVED")+(--($B$1:$B$40="CLOSE"))))

Also, adjust your ranges as necessary. My example used rows 1 through 40. And notice I used "CLOSE", not "CLOSED", since none of your example data said "CLOSED".

[RAMDAS]

Thanks Paul.It works.

I have changed range to 23 from 40
=SUMPRODUCT(--($A$1:$A$23="A")*(--($B$1:$B$23="RESOLVED")+(--($B$1:$B$23="CLOSE"))))

If it is not to much of trouble to you,I have one more requirement instead of range can I give whole column name.since no. of rows are variable in my case.

Thanks in advance.
~ram

[Paul]

With SUMPRODUCT, you cannot use an entire column reference (like A:A or B:B). You can, however, use large references such as A1:A65000, B1:B1800, etc., just not full columns. Just pick a number that is a few hundred greater than you'll ever expect to need.

[arthurbr]

You can also use dynamic ranges

[RAMDAS]

I thought so .Thanks once again

Regards
~ram