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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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"))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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
Back
Top