Replacing COUNTIFS with COUNTIF

[Ashish G]

Hi,
I prepared a report in Excel 2007 that uses the Yearfrac and countifs
functions to generate values. Unfortunately, my target audience has Excel
2003 so they get the Name# error in all the fields with the YearFrac/Countifs
formulae. Is there any way I can re-work the formula to get the same results?
Here is the sample data on one worksheet (PSG) and the formula I am using:
A B C D
E
Emp Code Name Designation Joining Date Tenure (years)
311 Mark Jacob Sr. QAP 5/17/2004 3.55
100 Joshua Smith QAP 3/15/2005 2.73
144 Rahul D QAP 3/1/2006 1.76
434 Bradshaw, G QA Lead 10/5/2006 1.17
237 John Joseph BA 12/4/2003 4.01

to get the value of the tenure in column E, i was using:
=YEARFRAC(D3,TODAY(),3)
On the second worksheet ('Summary'), to find the number of employees with
designation QA and tenure > 2 years, I was using:
=COUNTIFS(PSG!C2:C100,"=QAP",PSG!E2:E100,">=2")+COUNTIFS(PSG!C2:C100,"=QA
Lead",PSG!E2:E100,">=2")+COUNTIFS(PSG!C2:C100,"=Sr. QAP",PSG!E2:E100,">=2")

Similarly, for designation QA and tenure between 1 and 2 years:
=(COUNTIFS(PSG!C3:C100,"=QAP",PSG!E3:E100,">=1",PSG!E3:E100,"<2"))+(COUNTIFS(PSG!C3:C100,"=Sr.
QAP",PSG!E3:E100,">=1",PSG!E3:E100,"<2"))+(COUNTIFS(PSG!C3:C100,"=QA
Lead",PSG!E3:E100,">=1",PSG!E3:E100,"<2"))

Please let me know if I can use some other functions (instead of YEARFRAC
and COUNTIFS) to get the same results.

Thanks,
Ashish

 

[T. Valko]

Use SUMPRODUCT:

=SUMPRODUCT(--(ISNUMBER(MATCH(PSG!C2:C100,{"QAP","QA Lead","Sr.
QAP"},0))),--(PSG!E2:E100>=2))

=SUMPRODUCT(--(ISNUMBER(MATCH(PSG!C2:C100,{"QAP","QA Lead","Sr.
QAP"},0))),--(PSG!E2:E100>=1),--(PSG!E2:E100<2))

 

[Ashish G]

That worked really well.
Thank you so much!

Use SUMPRODUCT:

=SUMPRODUCT(--(ISNUMBER(MATCH(PSG!C2:C100,{"QAP","QA Lead","Sr.
QAP"},0))),--(PSG!E2:E100>=2))

=SUMPRODUCT(--(ISNUMBER(MATCH(PSG!C2:C100,{"QAP","QA Lead","Sr.
QAP"},0))),--(PSG!E2:E100>=1),--(PSG!E2:E100<2))

 

[T. Valko]

You're welcome. Thanks for the feedback!