SUMIF when you have a range

[politicalangel]

Hi,
I have 2 tables- one that has the key ( tells us if an employee is term and active) and this drives the calculation table to give me the amount per month.

What I need is a formula by each month that will only give me the active employees of that year. So for example in the month of Feb-- it should only sum the total of only the active employees at year end(Dec). If they were term. within the year I would not need them included.

Thanks...
See attached spreadsheet for sample

 

[Somendra Misra]

Hi politicalangel,

To get total count of employees who remain active for the whole year say from Feb to Dec try below formula.

=SUM(IF(MMULT(--(B4:L11="Active"),(ROW(1:11)^0))=COUNTA(B3:L3),1,0))



Regards,

 

[politicalangel]

Hi politicalangel,

To get total count of employees who remain active for the whole year say from Feb to Dec try below formula.

=SUM(IF(MMULT(--(B4:L11="Active"),(ROW(1:11)^0))=COUNTA(B3:L3),1,0))



Regards,

Thanks for your help... question though.. if I needed the total amount and not count how would I do that..?

 

[Somendra Misra]

Total amount can be =SUM(P4:Z11)

Regards,

 

[politicalangel]

Total amount can be =SUM(P4:Z11)

Regards,

Am sorry... I should have been more clear... the 1st formula worked but it gave me the count.. what I need by month is the amount not count by month for only active employees for the year.
So Feb total should be less any term for the year, march the same thing and so on so forth.

please advice..

 

[Somendra Misra]

Can you post manual result what you should get in Feb, Marc...?

Regards,

 

[politicalangel]

Can you post manual result what you should get in Feb, Marc...?

Regards,

Please see attached I have revised it.... the highlighted I what I was trying to create a formula for. The reason is that I have over 6 years of data side by side.. and the results less the term is what is going to drive the next phase of the model am building.

Thanks for your help

 

[Somendra Misra]

Try below formula in P14 and copy right.

=SUM(IF(MMULT(--($B$4:$L$11="Active"),(ROW($A$1:$A$11)^0))=COUNTA($B$3:$L$3),P$4:P$11,0))

Regards,

 

[politicalangel]

Thanks .... This would make sense if my array matched.. but I have over 700 rows of data so my rows and column do not match, hence I dont think I can use MMULT function.. ( or can I?)

 

[Somendra Misra]

See the attached file, with the MMULT formula on row 16 and alternate formula on row 17. For row 17 formula column AA is helper.

Regards,

 

[politicalangel]

Perfect-- worked great ... Thank you for your help

 

[Somendra Misra]

Glad you solved it welcome back anytime!!

Regards,