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")+CO UNTIFS(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",PS G!E3:E100,"<2"))+(COUNTIFS(PSG!C3:C100,"=Sr.
QAP",PSG!E3:E100,"=1",PSG!E3:E100,"<2"))+(COUNTIF S(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

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))

--
Biff
Microsoft Excel MVP


"Ashish G" wrote in message
...
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")+CO UNTIFS(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",PS G!E3:E100,"<2"))+(COUNTIFS(PSG!C3:C100,"=Sr.
QAP",PSG!E3:E100,"=1",PSG!E3:E100,"<2"))+(COUNTIF S(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

That worked really well.
Thank you so much!

"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"Ashish G" wrote in message
...
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")+CO UNTIFS(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",PS G!E3:E100,"<2"))+(COUNTIFS(PSG!C3:C100,"=Sr.
QAP",PSG!E3:E100,"=1",PSG!E3:E100,"<2"))+(COUNTIF S(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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ashish G" wrote in message
...
That worked really well.
Thank you so much!

"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"Ashish G" wrote in message
...
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")+CO UNTIFS(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",PS G!E3:E100,"<2"))+(COUNTIFS(PSG!C3:C100,"=Sr.
QAP",PSG!E3:E100,"=1",PSG!E3:E100,"<2"))+(COUNTIF S(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