Adding Multiple COUNTIFS into one formula OR am I doing this all wrong???

Hello Forum Experts,

I would really appreciate any help that would help me to complete this portion of my workbook. I am stuck trying to write a formula and have tried many ways with SUM and COUNTIFS. I am trying to count how many times an employee and another criteria appear in a sheet, but the second criteria has many variations. I am trying to look at certain information in the second criteria, i.e.the initial code that appears and the month and date. The data is setup in the following manner:


Column J- examples - not in order

Smith, John STAR MANUFACTURING CORP - SMC
Johnson, Deb STAR MANUFACTURING COR
Reed, Simon STA
Coleman, James STAR MANUFAC

Column A- examples - not in order

G, 12/01/2013, 55432 able to meet deadline on 7/20/13
D,09/11/2013,2231444 SENT EMAIL TO SUPPLIER
AQ, 9/25/13, 553211
AQ, 09/13/2013, 7564
AQ,09/12/2014,55432, No reply from email on 8/3/13

*All data is not static. It changes on a weekly basis.


Here is the formula that I have been trying to use:

=SUM(COUNTIFS('ESC'!$J:$J, "Smith, John*",'ESC'!$A:$A,{"AQ,09*2013*","AQ,9*13*","AQ, 09*2013*","AQ, 9*13*"}))

Here is the logic and basic thing I am trying to do:

Because people enter there data in different formats I am basically trying to add these COUNTIFS together

=COUNTIF('ESC'!$J:$J, "Smith, John*", 'ESC'!$A:$A, "AQ,09*2013*")
=COUNTIF('ESC'!$J:$J, "Smith, John*", 'ESC'!$A:$A, "AQ,9*13*")
=COUNTIF('ESC'!$J:$J, "Smith, John*", 'ESC'!$A:$A, "AQ, 09*2013*")
=COUNTIF('ESC'!$J:$J, "Smith, John*", 'ESC'!$A:$A, "AQ, 9*13*")
etc.

If anyone could help it would be most appreciated. I have been trying to work/research this for the past 8 hours. I also have other follow on questions but I would like to see how close I am. Thank you much!!!

Jeff

and yes I do not do this for a living and I am new to the forum.

I have looked at http://www.contextures.com/xlFunctions04.html#Operator and found the SUMPRODUCT section example and tried variations of that as well.

I appreciate your quick response. Unfortunately the workbook cannot be uploaded because it would be illegal for me to do so. It contains sensitive information that cannot be seen by the general public. Setting up a shell workbook with the same formatting I am using will take me many hours because the workbook is very large and I would have to triple check to make sure I am not uploading sensitive info.

I thought I spelled everything out fairly good with data,objectives, and formula(s). I am really just needing guidance in the right direction.

Originally Posted by codeslizer

Hi there buddy,

You were pretty close I must say. Basically you can accomplish this task by using COUNTIFS() and basically you don't need to SUM it up.
See attached working example based on your conditions.

Thank you for your reply. Instead of trying to reference the information on the worksheet you are saying i should create a list of criteria? Take a look at sheet 2 on the workbook I uploaded. Is there a way that I can grab the following information and count it without making any reference table: AQ,09*13* --> it is saying quote arrives on September 2013? But like i said in my original post there are a few variations of that type of cell criteria. Thanks again for helping!

Originally Posted by codeslizer

Hi there buddy,

You were pretty close I must say. Basically you can accomplish this task by using COUNTIFS() and basically you don't need to SUM it up.
See attached working example based on your conditions.

and also you set the formula like =COUNTIFS($A$2:$A$28,$E$3,$B$2:$B$28,$F$3) but I cannot have a set range of cells. I must reference each entire column, so can this work: =COUNTIFS($A:$A,$E$3,$B:$B,$F$3) along with what I asked on my previous post to you?

Originally Posted by codeslizer

Ahem.. uh.. was that last post for me? coz I didn't get the upload thing, are you unable to download the file or something? The attached file contains the use of COUNTIFS based on your example. Don't mind, am just a li'l confused with the last post..

No sorry! I was typing/sending that to the first gentleman that replied to my first post but you replied while i was typing so it looked like i was replying to you. sorry for the confusion. I havent been on a forum in a while and forgot to quote him.

@oeldere

Thank you for educating me. There is now a copy of something that is similar to the database that codeslizer created. I cannot create anything and put it on the forum from the computer I am working on (Too much to explain). I have edited and made comments to the one I am uploading now.

@oeldere and @codeslizer

Please see the FORECASTING sheet on the attached workbook. It basically sums up what I need to do along with my original post.

Thanks again to codeslizer for creating one real quick as I cannot upload created files from this computer.

@oeldere


It is not messy data, it makes perfect sense to what I and others do. The only variances within it is the date, but everyone types into the system differently. They only way to fix that is spend about 1-2 million for software changes (which will never happen) or tell 3000 people across 3 different agencies that they have to change how they type this one line of info (Which again, will never happen). SO... with the previous information I have given and with the attached workbook which i cleaned up a little more, do you have any Excel input you can give me at this point in time?

Cell B3 on the forecasting sheet has my formula that I am trying to work on. Currently it only counts one which it should be 6. The red sheet which will be hidden has reference data, am I typing this in correctly or do I need to do it differently.

@oeldere and @codeslizer

Thank you both for helping me. I really appreciate it.I have looked at each of the files that both of you sent me, but my eyes are now crossing because it is now 5:45am (United States - Eastern Time) the next morning and i have not slept. I will look at both files further after I wake up and send each of you comments on what you have written. I apologize, I am very tired. Thank you again and talk to you very soon...

Try this:

No helper columns or VBA is required.

=SUMPRODUCT((LEFT(Code,2)="AQ")*(LEFT(Name,LEN($A3))=$A3)*(TEXT(TRIM(MID(Code,4,FIND(",",Code,4)-4)),"mmmmyyyy")=B$2&LOOKUP(1E+99,$B$1:B$1)))

See the attached file

Originally Posted by Teethless mama

Try this:

No helper columns or VBA is required.

=SUMPRODUCT((LEFT(Code,2)="AQ")*(LEFT(Name,LEN($A3))=$A3)*(TEXT(TRIM(MID(Code,4,FIND(",",Code,4)-4)),"mmmmyyyy")=B$2&LOOKUP(1E+99,$B$1:B$1)))

See the attached file

THANK YOU SIR! That was the most simplistic way and it works like a charm in the one you made. Now I am trying to incorporate it into my actual database but I am getting a #NAME? error. Any suggestions?

sorry ma'am....

=SUMPRODUCT((LEFT(Code,2)="AQ")*(LEFT(Name,LEN($A3))=$A3)*(TEXT(TRIM(MID(Code,4,FIND(",",Code,4)-4)),"mmmmyyyy")=B$2&LOOKUP(1E+99,$B$1:B$1)))

The formula above works on the example worksheet and does exactly what I want it to, Thanks to @Teethless mama! I also would like to thank @oeldere and @codeslizer for working/helping with me many hours last night to fix my issue. @codeslizer's second example worked but it was too complicated for me to understand and incorporate into my workbook.

I am now stuck trying to plug the formula into my actual workbook. I cannot upload the actual workbook because it would be illegal for me because it has propriatary and sensitive information on it. Here is the basic information and what I am trying to do.

-Instead of Column A being named "Code" and Column B being named "Name" on my actual workbook are:

example Column A is actually: named "BUYER_NOTES" and it is Column AI
example Column B is actually: named "BUYER NAME" and it is Column J

-Instead of the sheet being named ESC:

it is actually named "FAB FROZEN"

Their are about 60 buyer names on the actual workbook as well

Can anyone help me to understand the formula a little better so i can input this into my actual workbook?

@oeldere

Unfortunately I am on a timecrunch (2 days to finish) and I do not know how to incorporate that into my actual workbook. I have only used a pivot table a couple of times and do not know how to set it up to work with what both of you did. Any suggestions?

@oeldere

Yes I saw that. I know how to create a pivot table but do not understand what else to do about @codeslizer's example file. It works but i do not know how to incorporate it. Not sure what else to do. @teethless mama's file worked as well but again, i do not know how to recreate it for my workbook.