# 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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

Replies
7
Views
216
Replies
2
Views
74
Replies
3
Views
182
Replies
1
Views
192
Replies
2
Views
292