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

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
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
 
Master Excel Bundle

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.

Forum statistics

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