Compare 2 columns then give count

I need a formula to look in column B and find all "jun" (or anything else that I put in there) THEN look in column J and find only all "d" (d is one of 3 choices there) that correspond to the "jun" then give me the count of the "d".


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

Sounds like you could use SUMPRODUCT, try like

=SUMPRODUCT(--(B2:B100="Jun"),--(J2:J100="d"))

Note: I'm assuming that column B contains text, if it contains dates then you might need to change to

=SUMPRODUCT(--(MONTH(B2:B100)=6),--(J2:J100="d"))

daddylonglegs:

Yes. The first one is working...I think.

It works great on a practice page but when I use it on the real thing it's not seeing all of the d's that I can physically count.

I haven't figured out why yet, but the equation (first one you gave me) should work when I get the problem solved.

Thanks!

This is crazy! Out of 5 months I've done on the actual spreadsheet...none of them come out correctly. The totals vary between 1 and 5 in difference, but it works on the test page. ???

Only text. First 3 letters of each month. Just used as an identifier as there could be 5 in one month and 25 in another month. With the identifier I pull out all of whatever month then find how many d's or l's (L's) {column J on my spreadsheet} then that number is sent to another sheet for different information. I just used "d" to explain the situation.

Try breaking out the formula to see where the problem lies.

Please Login or Register  to view this content.

should match your Jun count and

Please Login or Register  to view this content.

should match your d count. If it's your d count, look for blanks before or after your d's. Excel is looking for an exact match.

If you can, highlight the column with the d's and search for blanks and replace with nothing.

Any of this help?

ChemistB

ChemistB,

Sorry I haven't gotten back sooner. I'm just home off of the road for a couple of days (OTR truck driver) and have TONS of stuff to pack into the 2 days.

I will try what you said and see if it works. I don't think that's it though. The columns are only big enough for the letters listed above and they are centered so they go in the same every time.

It may help me find something though. We'll see.

Thanks for the help.

ChemistB,

I think I figured it out w/your help.

For each week of the month there is one of many different headings listed as D/L/U. This is for the J Column in my example. If there is a "d" in that column directly beneath the D/L/U it picks it up as (not sure what you call it) predictive text(?) and when you hit the space key so it just inserts the d as opposed to letting the D/L/U stay, that d is like a fraction of a space off of center of the rest of the d's in the column.

Not even sure how I fixed it now. I just kept juking with the backspace, d, and enter key till the d stayed in the proper position then it would be counted correctly.

Any ideas how to keep that from happening? Hate to turn off the predictive text as it comes in very handy in other repeated entries on the spreadsheet.

I also meant to ask why in the formula you can't just put B:B to look in the entire column. I've done that before but it doesn't seem to work here.

My Bad! That was daddylonglegs that helped me with the problem.

If there is a "d" in that column directly beneath the D/L/U it picks it up as (not sure what you call it) predictive text(?) and when you hit the space key so it just inserts the d as opposed to letting the D/L/U stay

When you hit the space key it enters a space after the d. Use the backspace key instead. That will leave the d without spaces.

ChemistB

Originally Posted by George

Using the same idea, (first looking at column B for the letters JUN)... how would I get it to see only numbers in a different column, say column T and then get a total and/or average for numbers that correspond with JUN?

For Totals;

Please Login or Register  to view this content.

For Average;

Please Login or Register  to view this content.

Does that work?

ChemistB

The Total worked, the Average says: "You've entered too many arguments for this function"

Any ideas on the average?

Well....it is giving me a number now, but it isn't the average. This will give me something to work on and see if I can find where the problem is.

Thanks, ChemistB

George4

OK. It's working on a test page. I must be doing something wrong. Still working on it.

Here's the problem.

It's counting the n/a's with the numbers. Is there a way for it to see only the numbers in the column and not "words"?

Sweet! You rock! I think that's what you were going for before but lacked the last part.

How does someone learn this stuff?

I'm so sorry! I messed up.

I compared the wrong cell on your answer and thought that your last answer was correct but it was the same as the previous formulas answer.

The attachment should help. I should have done this at first probably.

As a refresher to what I'm needing:
First look at column B for the letters JAN. Then, see only numbers in column C and then get an average for numbers that correspond with JAN. It's still counting all criteria in column C and dividing by that number rather than just the numerical total.

The attachment will make this a lot clearer.

Thanks Again,
George4

EUREKA!

That works on everything I've tried it on so far....that must be it!

Now I'm going to study it and see how it works and how I can use it for other statistics I'd like to track.

Thanks to all who helped,
and thank you Mark@Work

Hope this one was titled well enough as to not incur another infraction.

If I could find the drop down I would mark this thread "solved".