Countif with 2 conditions

[Guest]

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!

 

[Guest]

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)*(P210=A2))

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

XL2002, WinXP

 

[Guest]

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=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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

OK....I'm feeling a bit like a mushroom here. <vbg>

I'm having no trouble getting the formula to work with my test data, which
includes alpha, numeric, and alpha-numeric data. So, now I have some
questions:

Exactly what data is in columns C and P?
Are they calculated by formulas?
(If yes, are any of those values resolving to an error?)
Are they dates formatted as Q1, Q2, etc?
How are cells C2 and P2 being populated?

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

What does "feeling a bit like a mushroom" mean?

I can force this to work if I type in results in the cells, but I'm using
calculations from other cells.

Column C is strictly numbers - the company ID
Column P is a calculated field. It's based on 4 other columns.

Column L has YES or NO or #N/A (results from a VLOOKUP)
Column M is a date or #N/A (results from a VLOOKUP)
Column N is a calculation, IF the date is in Q1, then "Q1", "Q2", or
"InvalidDate" and #N/A
Column O is a calcuation, IF the date is Q1 and L1 etc...

I know this sounds very confusing, but I don't know of another way to do
this. I'm wanting to learn more about Excel and VBA, because I know if I
could just right some logic I wouldn't need all these columns and
calculations, But, for today, this is what I have to work with.

I bring this data into the file via Access every month, so a Pivot Table
would not work directly.

Again, thanks for your time!

 

[Guest]

I'd recommend that you take steps to prevent the #N/A values from corrupting
the rest of the calculations.

A typical approach is:
=IF(ISNA(VLOOKUP(A1,B1:B10,2,0)),0,VLOOKUP(A1,B1:B10,2,0))
That formula replaces error values with zeros

You could either put that kind of formula in columns L through O, or just
use it in Col_P.

Does that help?
(Post back with more questions)
***********
Regards,
Ron

XL2002, WinXP