Counting with multiple criteria from multiple lists

[ncurran217]

I have received help with parts of this already, but there is one last formula I cannot get a hang of. Attached is my sample sheet. I am trying to get a count of how many sales on a particular date, within a particular department of reps on that day and only counting certain promo codes, as well as if the verifier column is blank (actually with my data has to be "").

In columns L through O, I have the sales data.
In column Q is the list of the verifiers, which in my case doesn't matter as I want to count the sales where there is no verifier.
In column R is the list of Promo Codes I want to count.

In columns T through W is the list of reps in department A.
In columns Y through AB is the list of reps in department B.

Now within these departments reps my move departments or leave of some sort, so I want to make sure I include only those reps on that date.

I have manually put in Columns C and F what the actual sales should be, manually adding them up. In B3, I have tried manipulating the formula to add in the rep by date part, but I get #VALUE! error.

Hopefully I have added everything to understand what I am going for. Thanks in advance!

Edit:

Credit to Tony Valko, for helping me with my original formula!

[Hurricanefly]

Do you mean in cell C4 the answer is 2, because there are 2 "111" on "01/06/2013"? if so you could use this

Please Login or Register  to view this content.

then change it to other criteria. If I have read that wrong sorry, it doesn't sound hard what you want to achieve. Forget that I have read it wrong.

[ncurran217]

In C2 is Sales w/o Formula. As for you question yes there are 2 deals with 111 on 6/1/13, but that is not all that I am trying to include. I am trying to count if verifier is "", all the promo codes in column R, and then for the relative department the list of reps in the column on the specific date. So as for the sales for Department A on 6/1/13 with the verifier marked as "", the promo codes 111,222 & 444, including the reps in the department A on 6/1/13 (Brittney, Stephanie, Craig, Dylan & Crystal) there are 4 sales.

[ncurran217]

Hopefully this is clear on what I am looking to do. Let me know if there any questions on what I am trying to do!

[oeldere]

Not sure what you're up to.

But i tried an solution with an pivot table.

[ncurran217]

I am trying to get just a count with all this criteria, without having to manually put the criteria into the formula. Where I can have just a list of the criteria and I can update it if necessary and not have to worry about accidentally messing the formula up trying to add or delete something.