Formula Help

missaimee

Board Regular
Joined
Dec 15, 2004
Messages
136
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Code:
=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

Board Regular
Joined
Dec 15, 2004
Messages
136
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Dec 15, 2004
Messages
136

ADVERTISEMENT

I tried that too and it's still not working :(
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Dec 15, 2004
Messages
136

ADVERTISEMENT

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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))
 

Forum statistics

Threads
1,141,678
Messages
5,707,782
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top