Thread: Formula looking in 2 columns

I need a formula to look in column B and find all "jun" THEN look in column J and find only all "d's" that correspond to the "jun" then give me the count of the "d's".

This is an example. With the formula I can do a variety of things to get info that I'd like to have.

Thanks,
George4

Hi George,

I still pretty new to this and I hope I can do my part to help as well. This is a great forum for me to get help from.

I suggest using a helper column to create a unique ID for you to do a countif function.

Here's a sample for you. See the attached file.

HTH,
J

You can do this by using only one array formula:

=SUM((A1:A10="d's")*(B1:B10="jun"))

But remember press Ctrl+Shift+Enter after typing it, instead of just Enter.

jtan:

It looks like by making a helper column you're actually going back and putting the answer (helper column criteria) next to the ones that you want it to count. That kind of takes the "automatic" out of a computer formula.

Let me know if I've missed something.

sglife:
I tried your formula where I should have had 2 "d" corresponding with 2 "jun". The third "jun" I put an "x". The answer I got was "0" instead of "2".

Any thoughts?

george4

I think its because you have changed the formula cell after or before you keyin the "x", remember everytime after you changed or just click inside the formula cell, press a "Ctrl+Shift+Enter".

sglife:
OK. I switched the 2 parts of the formula and it worked...BUT...again, if I have to press ctrl+shift+enter every time...it doesn't make it automatic.

The answer to the formula is actually used in a formula on another excel page. (Not sure I'm explaining this right) So it has to (1) look for and (2) match up the correct criteria, then (3) post the count for the answer. The answer, in turn, is then "picked up" by another excel page and used in a formula there where I use it to find a percentage.

Thanks for all your help.
I think we're getting close.

Thanks for your help. I got the answer to the problem here: http://www.excelforum.com/excel-misc...ive-count.html