Communicating between two excell columns using functions ( no macros) ?

[diego2291]

Comm2Colum.jpgComm2Colum.jpgSo I have two columns of data. Column D changes from a letter to a blank depending on the user inputs of column B. Column E has numbers designated to the letters that are present in Column D. My problem is as follows:

- I need to create a function that counts how many cells in Column E still have a adjacent letter in Column D after the user is done with the inputs in Column B.

SO BASICALLY HOW DO I GET THESE TWO COLUMNS TO COMMUNICATE?
TWO DAYS IS FAR TOO LONG FOR THIS PROJECT.

So far what I tried doing was to put a count function inside an if function to count the number of cells still having a letter in Column D(condition for if function). However, escel is not able to do this because the if function goes to false once it hits the first blank. thus skipping the count and making the result a 0.

PLEASE HELP!

Attached is an example of what I am talking about.

So the result should reflect how many times 20 repeats iff their correspoing D-cells have a character and not a blank. ( Result = 2)

[tigeravatar]

diego2291,

Welcome to the forum!
Using your example image, give this a try (good for all versions of Excel):
=SUMPRODUCT((D2:D6<>"")*(E2:E6=20))

For Excel 2007 and higher:
=COUNTIFS(D2:D6,"<>",E2:E6,20)

[diego2291]

Hey tigeravatar,

thanks for the welcoming. I will be posting a small bio later on today but right now I am trying to solve this problem.

I have tried using COUNTIF/COUNTIFS before. However, COUNTIFS(D2:D6,"<>") is not skipping the blanks. It counts the blanks and puts it in the result. Therefore giving me a bad result. Any more ideas? =)

[tigeravatar]

Sorry, forgot an asterisk:
=COUNTIFS(D2:D6,"<>*",E2:E6,20)

[diego2291]

Thanks for the help. I had to change the "<>" to "=" , which gave the right answer. Can you please explain to me the function of the asterik "*" in the line of code you wrote?

[tigeravatar]

It's a wildcard character, to test if a cell isn't blank.

[diego2291]

Oh okay thanks. I just never used this character before. I have something else to ask you. I am currently working on a project involving VB for Excel 2007. Now I only have two months to finsh this project and my knowledge of Visual Baisc is well....very basic. Do you know of a good book that might be able to expand on my knowledge that wont be too time consuming?

[tigeravatar]

Unfortunately, I'm self-taught, so I can't really point you anywhere specific. This thread has many good links though:
http://www.excelforum.com/excel-prog...materials.html