Need one column total if criteria meets another columns criteria

**jebrown** · 04-24-2013, 06:36 PM

In Column F I have various dates that are in this format 11/15/2013. In Column E I have Y or N. I need to count how many Y's for each month. eg. (5) April entries in column F, (3) had a Y in column E. My formula to get the month is =SUMPRODUCT(1*(MONTH(Bell!F11:F50)=11)) Oh, yeah these are from another sheet. I have tried =COUNTIF(Bell!E11:E50,"Y")*AND(SUMPRODUCT(1*(MONTH(Bell!F11:F50)=11. I can get the correct month or all the Y's, but not the Y's and date combo. I am new with excel so please put in very simple terms. I know a pivot table would be easiest, but I didn't set this up. I am trying to avoid counting each sheet and fill in the blank every month. Thanks for any help you can give me.

Column E Column F
Y 11/3/2012
Y 11/9/2012
N 11/18/2012
Y 12/6/2012
I would need the answer to be 2 in this example.

**Lemice** · 04-24-2013, 06:49 PM

Hello,

Assuming your data is in Column E and F, starting from row 1, paste this formula in G1

Please Login or Register  to view this content.

the --( added before a logic statement is to make it returns 1 and 0 instead of TRUE and FALSE. SUMPRODUCT will multiply both of the criteria afterward, and sum them together.

If a row matches both of this criteria, it will be 1*1 = 1; and if either or both of the criteria isn't met, it will be 1*0 or 0*0 = 0.

The 11 in the formula is the month you want to check.

**jebrown** · 04-24-2013, 07:23 PM

Thanks for the fast reply. I am not at work, but I will try it tomorrow. Since this is from a different sheet (Bell) would I write that (Bell!F1:F4) and (Bell!E1:E4="Y")?

**Lemice** · 04-24-2013, 07:29 PM

The formula above is checking the date and "y/n" column right on its left, on the same worksheet.

Once you pulled a data into a worksheet using a formula, other formulas in that same worksheet does not have to check the origin of the data anymore, but can use the cell with that data directly.

For e.g. you pulled dates into your column F from another worksheet, right? Then this formula can work directly with that column F, so you don't have to refer it back to where the data comes from.

But yes, you can always give the formula the worksheet path, and point it directly to the "Bell" worksheet.

**jebrown** · 04-24-2013, 08:47 PM

Ok, so I have about 30 sheets, each with different doctors names. Each sheet has "Criteria Met" Y or N for the answer that is Column E.
Column F has the date. On another sheet named "Monthly Total" there is a column for "How many charts in (will say) "November". The other
column is "How many of charts met criteria". So the layout looks something like this:

Doctor Charts in November Met Criteria
Brown 5 3
Smith 4 4
Jones 7 5

The doctors sheet looks like something like this:
Brown

MRN Question 1 Question 2 Question 3 Met Criteria Date
1234 Y Y Y Y 11/5/2012
4567 Y Y Y Y 11/7/2012
7890 Y N Y N 11/12/2012
2345 Y Y Y Y 11/17/2012
3456 Y Y N N 11/21/2012
4567 Y Y Y Y 12/4/2012

Does that help make it any clearer? I'm very new with this and not always sure if I explain it correctly. I really appreciate your help.