Ifs ands countifs

Hi,

I've attached a file for clarification.

I would like to get a total month count

Column C = I8 and
Column D = 3284 and
Column E = 600000 and
Column F - I3

I would like to add up each occurrence that this happens and add them together.

Thanks,

Helder

I think you mean that you want to count them. If so, try this:

=COUNTIFS(C3:C14,I8,F3:F14,I3,D3:D14,3284,E3:E14,600000)

Hope this helps.

Pete

I actually want to subtract I3 from Column F. That would give me a month count. But I only want it to do that if everything else is true. If there are two rows that meet the first three then I want to add the month count together.

I don't understand what you want to achieve.

Pete

Column F is a person's end date. I want to subtract the end date from the current month end and get a month count. I know how to do that. For example employee A's end date is 12/31/12 and the current month end date is 9/30/12. That gives us 3 months. I want to do that for every employee that has a code of 3284 and that has an ID of AAA1234 and that has a sub of 600000. In this case only one employee has all three so the cell would be 3.

However when more than one employee has code 3284 and same ID and same sub then I want to add up all the months for example if we take ID AAA1235 there are two employees with code 3284 and sub 600000. One has an end date of 12/31/12 so that would be 3 months and the other has an end date of 1/31/13 so that would be 4 months. I want the value to be 7 if that's the case.

Okay, this seems to do what you want (well, on your examples anyway):

=SUMPRODUCT((C3:C14=I8)*(D3:D14=3284)*(E3:E14=600000),(MONTH(F3:F14)-MONTH(I3)+12*(YEAR(F3:F14)>YEAR(I3))))

Hope this helps.

Pete

Thank you. Works perfectly.