Ron,

It's getting closer.

It looks like SUMPRODUCT does not like alpha characters (P2 column, made up
of Q1, Q2, etc) I changed them to Q1=1, Q2=2, Ineligible=0 which works, but I
have some columns that are "#N/A"s. How do I handle that? Can't I make the
#N/A's = 0 as well?

Yes, it appears SUMPRODUCT likes defined ranges. That look like it worked.
Thanks for continuing to help.



"Ron Coderre" wrote:

I believe the problem is that SUMPRODUCT cannot use an entire column.

Try this:
A3: =SUMPRODUCT(($C$2:$C$65536=C2)*($P$2:$P$65536=P2))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"DTTODGG" wrote:

Thank you for your quick reply. I don't understand how this works, but it
sounds like it's correct, I'm just missing something.

A1: (a company id) these are numbers, (433, 291, etc) in Column C
A2: (a quarter) these are text ("Q1, "Q2", "Ineligible" and #N/A) in Column P

When I use the A3 below, can I use:
A3: =SUMPRODUCT((C:C=C2)*(P:P=P2))

Does Column P need to be numeric? It's currently text, see above.
Can I use C:C rather than C2:C10 a defined range?

Do I need to use the {} brackets?

Thanks again.


"Ron Coderre" wrote:

See if this gets you headed in the right direction:

A1: (a company id)
A2: (a quarter....eg Q1)

This formula counts the number of times that combination occurs
A3: =SUMPRODUCT((C2:C10=A1)*(P2:P10=A2))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"DTTODGG" wrote:

Hello -

I'm not certain how to explain what I'm looking for. I don't know much about
arrays, but I think that might be what I need.

I have a sheet that has:
Column C = Company ID Number
Column P = Eligibility Quarter (Q1, Q2, Q3, Q4 or ineligible)
I would like Column Q to total the number of:
Q1's for Company A if C=Company A and P=Q1
Q2's for Company A if C=Company A and P=Q2
Q1's for Company B if C=Company B and P=Q1
etc...
I know this will have repeating values in the columns, but, I need a number
it the column so I can do a calculation in Column W, based on the number in
Column Q.

So, Column W looks at Column Q. If Q=10, then W is a certain offer, if Q=50,
then W is another offer.

Thank you so much!