Could be very simple - PLS help

ntruong

Active Member
Joined
Aug 4, 2003
Messages
261
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.
 

Some videos you may like

Excel Facts

Links? Where??
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
Joined
Jan 1, 2003
Messages
2,062
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
Joined
Oct 4, 2004
Messages
1,862
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
Joined
Aug 4, 2003
Messages
261
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.

Please any help. Many thanks.

Nee
 

ntruong

Active Member
Joined
Aug 4, 2003
Messages
261

ADVERTISEMENT

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
Joined
Oct 4, 2004
Messages
1,862
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
Joined
Jan 1, 2003
Messages
2,062
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
Joined
Aug 4, 2003
Messages
261
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.

Enjoy your day!

Nee
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,948
Members
412,299
Latest member
agentless
Top