# Could be very simple - PLS help

#### ntruong

##### Active Member
Dear Experts,

It is now 6:40 pm my time and I'm still in front of a large spreadsheet and having to match data from the many many columns there. I thought about a simple tip that can help from me having to sleep on my office floor tonight and tomorrow night, and ....

Here is the situation:
I have the word "key" but not in every single cell under col D, range D10:D100.
Then, I have X's but not in every single cell under E, range E10:E100.

Pls how do I get a formula for the total of all the "key's" that have an X right next to it. If a "key" does have an adjacent X, it should NOT be counted for the total. Assuming that I have 100 lines of data, I'd like to have the formula in cell D101.
I'm in fact dealing with 50 times the size of the example. I will no doubt bow to any help you can provide. Many many thanks! [Nee]

For example:
line 1 key (blank)
line 2 key X
line 3 key X
line 4 key X
In this case, my total for "key" equals 3.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### nbrcrunch

##### Well-known Member
Please use more descriptive subject lines. These forums are exported to helpfiles on a regular basis.

Use the SUMPRODUCT() function. Seeing as you didn't indicate columns or rows where the data lies, you'll have to convert the plain english version I present to your columns of data (exclude headers)

=Sumproduct(--(KEYSCOLUMN="keys"),--(XCOLUMN="x"))

#### RAM

##### Well-known Member
Or maybe this of I'm not all of the track:
Book8.xls
ABCD
1Key
2KeyX
3KeyX
4KeyX
53
6
Sheet5

Hope This Helps

RAM

#### ntruong

##### Active Member
Thanks for the write back but I'm afraid you missed my point:
I'm working with ranges, and not columns.
(like D10:D100) then comes some other data then continues with D150:D250
for example.

Nee

#### ntruong

##### Active Member

Thank you RAM, but thats not it.

I need to put the formula under the "key" col. and count the "key" if it has an X next to it. I dont need to know the # of X's.

Any idea?

Nee

#### RAM

##### Well-known Member
I know this isn't the best answer and you need to change your worksheet too, but it seems to work per your preferences, even though I know there must be a better answer:
Book8.xls
ABCD
1Key
2KeyXX
3X
4KeyXX
52
6
Sheet5

#### nbrcrunch

##### Well-known Member
the formula I gave you will work with the stated scenario you gave. As I indicated before, you need to replace my English words with your ranges.

If ranges in columns D & E hold you data, then

=Sumproduct(--(KEYSCOLUMN="keys"),--(XCOLUMN="x"))

would translate to...

=Sumproduct(--(D10:D100="keys"),--(E10:E100="X"))

#### ntruong

##### Active Member
Hello nbrcrunch,

Thought I had to sleep in my office but the carpet has flees ...
Anyway saw your formula this morning and it works beautifully!
Thanks so much for coming back and save me bunch of hours!!

RAM -- so appreciate so effort to help too.

Nee

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,991
Messages
5,834,764
Members
430,319
Latest member
Excelhelppll

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

### Which adblocker are you using?

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

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