Been fighting this formula for days now...hopefully someone can help.
In plain terms i have a large spreadsheet with a lot of data. I need a formula that says "if AA1 is equal to some cells in column h, then count the texts in in column k of those rows if it contains "81MC*" and if column L is blank.
See sample image below.
Sample.jpg
Try
=COUNTIFS(H:H,AA1,K:K,"81MC*",L:L,"")
Copy down
Life's a spreadsheet, Excel!
Say thanks, Click *
Try this...
=COUNTIFS(H2:H13,AA2,K2:K13,"81MC*",L2:L13,"")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you guys. That worked great...in my sample. Doesn't seem to work in my actual spreadsheet though. More than likely it is an issue with formatting. Thanks for all your help.
Can you post a SMALL sample file that shows that formula does not work?
In the sample file let us know what result you expect.
Info on sample...
Info on sample...
Ok, there are a couple of problems.
The COUNTIFS function is not available in files of the *.xls file format.
In column C of the ItemTrackingReport sheet the cells that appear to be empty contain char 32 space characters.
Get rid of those space characters. One way to do that:
Select the range C2:C482
Hold down the CTRL key then press the H key. That will open the Find and Replace userform.
Find what: enter a space character
Replace with: nothing, leave this blank
Replace All
Next, change the column headers:
G1: 81MC
H1: 208
I1: 11
Then, enter this formula in G2:
=SUMPRODUCT(--($A$2:$A$482=$F2),--(LEFT($B$2:$B$482,LEN(G$1))=G$1&""),--($C$2:$C$482=""))
Copy across to I2 then down to G10:I10.
G2: =COUNTIFS(A:A,F2,B:B,"81MC*",C:C," ")
H2: =COUNTIFS(A:A,F2,B:B,"208*",C:C," ")
I2: =COUNTIFS(A:A,F2,B:B,"11*",C:C," ")
copy down
Thanks so much Tony! I had no idea it had space characters in the cells. It is an internet generated report exported to excel, so i knew there were some kind of "format" issues. Thanks again.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks