Formula Help

[missaimee]

I am trying to achieve the following:

I have a spreadsheet with columns that have the state & the type of a request. I am trying to find out how to do a formula that can find 2 different words in 2 different columns and if both are found, count the total. For example:

Column A Column B
CA DOI
TX BBB

On the other worksheet I need it to count how many instances DOI & CA are found in each column and enter that number in the cell. Can someone help me with this. I can't get my head around it.

 

[njimack]

=SUMPRODUCT(--(Sheet1!A1:A100="CA"),--(Sheet1:B1:B100="DOI"))

Ideally, you would replace "CA" and "DOI" with cell references containing the state and request type you need to count.

 

[missaimee]

I can't get it to work. I made a reference column for the states and types. My formula looks like this and it's coming back with a 0 when there are thousands of records that should be there

=SUMPRODUCT(--('Q123 Totals'!H2:H1093="1"),--('Q123 Totals'!K2:K1093="1"))

I'm not sure what is wrong. Any thoughts?

 

[NBVC]

If your searching for numerical matches don't put the number in quotes:

=SUMPRODUCT(--('Q123 Totals'!H2:H1093=1),--('Q123 Totals'!K2:K1093=1))

 

[missaimee]

I tried that too and it's still not working

 

[NBVC]

Are you looking for 1's or states and request types as you mentioned initially?

If you are actually looking for text strings, then the sample as per njimack should work for you.

Ensure your ranges cover your entire data too!

 

[missaimee]

I changed them to numbers because the pp said it would be better. So I am looking for numbers.

 

[NBVC]

I changed them to numbers because the pp said it would be better. So I am looking for numbers.

Actually he said cell references, which means cell address that house the text strings you are looking for.

E.g. If X1 houses CA and Y1 houses DOI


Then: =SUMPRODUCT(--('Q123 Totals'!H2:H1093=X1),--('Q123 Totals'!K2:K1093=Y1))

 

[missaimee]

I think I got it. Thanks for clearing that up for me.