countif multiple variables, multiple sheets.

[LeahDT1]

Hello everyone. First post and I don't even know if it is something that can be done without writing a few hundred different formulas.

What I am trying to do is have Excel count how many x's are in a specific (color coded) section of one sheet on each row that contains a person's name, then pull that number into another sheet (also color coded). I am fairly new at this and seem to have gotten myself in over my head. I've attached an example of the two sheets.

I don't know if there is one single formula that would work. Something like countif Sheet 1 header = Sheet 2 column A and (cell range of section) or color. Or if I would have to create a separate one for each person's name. I see a ven diagram in my head I just can't get Excel to see it.

Let me know if I'm not making any sense.

Thank you all in advance.

~Leah

[NBVC]

If you can reformat you date ranges in column A of Sheet1 to something like: Nov 16, 2008 - Nov 30, 2008

then you can use a formula, in B3 like:

=SUMPRODUCT((Sheet2!$A$7:$A$193=Sheet1!B$1)*(Sheet2!$E$7:$F$193="X")*(Sheet2!$B$7:$B$193>=LEFT(OFFSET($A3,-ROWS($A$1:$A1)+1,0),FIND("-",OFFSET($A3,-ROWS($A$1:$A1)+1,0))-1)+0)*(Sheet2!$B$7:$B$193<=MID(OFFSET($A3,-ROWS($A$1:$A1)+1,0),FIND("-",OFFSET($A3,-ROWS($A$1:$A1)+1,0))+2,255)+0))

copied down and across the first table....

You can then copy that formula to the 2nd table, but would have to change the ranges to match your Sheet2, section 2, then copy down and across

and repeat for each section's table.

[LeahDT1]

Yeah, I had forgotten about the date ranges when I made the original post. That sounds like it will work. Unfortunately this isn't my only project but I should have time to plug that in later today and I'll let you know what happens with it. Thank you very much for the quick reply. This forum is a great resource for all skill levels.