Replacing COUNTIFS with COUNTIF
 

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

Replacing COUNTIFS with COUNTIF
 

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

Replacing COUNTIFS with COUNTIF
 

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

Replacing COUNTIFS with COUNTIF
 

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