Sumif or Sumproduct?
 

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~

Sumif or Sumproduct?
 

=SUMPRODUCT(--(E4:E25="Location"),--(F4:F25=1),--(F4:F25<=3))
--
HTH,
Barb Reinhardt



"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~

Sumif or Sumproduct?
 

I'm unclear as to what you want. I see 1, 2, 3, 4 and NP, yet you're testing
for "1st", "2nd" and want to test for "3rd". Do you want the answer for the
data in your message to be 5? If range contains numbers 1, 2, 2, 3, 3, 4, NP
then:

=COUNTIF(range,"1")+COUNTIF(range,"2")+COUNTIF(ran ge,"3")

Tyro

"Helpless in Colorado" <Helpless in
wrote in message ...
Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please
help~

Sumif or Sumproduct?
 

=COUNTIF(D11:D16,1)+COUNTIF(D11:D16,2)+COUNTIF(D11 :D16,3)

D11:D16 is the area that has the values for years in company. You may want
to name that range by selecting that are, then click up in the name area,
then type years and hit enter. After doing that, you can just type years
instead of D11:D16 when entering the formula.

"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~

Sumif or Sumproduct?
 

If you need a subtotal for each location, that will take a little more work.
Do you need subtotals?

"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~

Sumif or Sumproduct?
 

Barb,

That is returning a "0" value. What if I take out the location piece. Can
you tell me how to add up the 3 criteria (1, 2, 3) in one forumula? I have
appox 1964 rows in this spreadsheet with over 45 locations.

"Barb Reinhardt" wrote:

=SUMPRODUCT(--(E4:E25="Location"),--(F4:F25=1),--(F4:F25<=3))
--
HTH,
Barb Reinhardt



"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~

Sumif or Sumproduct?
 

Brad that worked!!! Thank you so much.

"Brad Vogt" wrote:

=COUNTIF(D11:D16,1)+COUNTIF(D11:D16,2)+COUNTIF(D11 :D16,3)

D11:D16 is the area that has the values for years in company. You may want
to name that range by selecting that are, then click up in the name area,
then type years and hit enter. After doing that, you can just type years
instead of D11:D16 when entering the formula.

"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~

Sumif or Sumproduct?
 

Hi Tyro,

That's the problem. I only want to add 1, 2, 3 employees not 4 and NP. I
need to add the 1, 2 and 3 employees at each location. I want the data to
answer the sum of the 1st ,2nd and 3rd year employees in each location.

"Tyro" wrote:

I'm unclear as to what you want. I see 1, 2, 3, 4 and NP, yet you're testing
for "1st", "2nd" and want to test for "3rd". Do you want the answer for the
data in your message to be 5? If range contains numbers 1, 2, 2, 3, 3, 4, NP
then:

=COUNTIF(range,"1")+COUNTIF(range,"2")+COUNTIF(ran ge,"3")

Tyro

"Helpless in Colorado" <Helpless in
wrote in message ...
Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please
help~

Sumif or Sumproduct?
 

I do not need subtotals. You hae saved me a lot of time! I really
appreciate it!

"Brad Vogt" wrote:

If you need a subtotal for each location, that will take a little more work.
Do you need subtotals?

"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~

Sumif or Sumproduct?
 

You have to replace "Location" with the actual location - ie "Franktown" or
"Jacobs" in the formula.

Tyro

"Helpless in Colorado" wrote
in message ...
Barb,

That is returning a "0" value. What if I take out the location piece. Can
you tell me how to add up the 3 criteria (1, 2, 3) in one forumula? I
have
appox 1964 rows in this spreadsheet with over 45 locations.

"Barb Reinhardt" wrote:

=SUMPRODUCT(--(E4:E25="Location"),--(F4:F25=1),--(F4:F25<=3))
--
HTH,
Barb Reinhardt



"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only)
for
each location. What formula do I use? I am currently only able to add
2
criteria to one forumla:
=SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please
help~

Sumif or Sumproduct?
 

Here is your answer, in 1 formula for 3 locations, AAA, BBB, & CCC.

If you have more locations, simply "ADDON".

=SUMPRODUCT(--(B7:B15="AAA"),--(C7:C15=1),--(C7:C15<=3))+SUMPRODUCT(--(B7:B15="BBB"),--(C7:C15=1),--(C7:C15<=3))+SUMPRODUCT(--(B7:B15="CCC"),--(C7:C15=1),--(C7:C15<=3))

That's All.

FLKulchar
"Helpless in Colorado" wrote
in message ...
I do not need subtotals. You hae saved me a lot of time! I really
appreciate it!

"Brad Vogt" wrote:

If you need a subtotal for each location, that will take a little more
work.
Do you need subtotals?

"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only)
for
each location. What formula do I use? I am currently only able to add
2
criteria to one forumla:
=SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please
help~

Sumif or Sumproduct?
 

Does the same thing and saves a few keystrokes:

=SUM(COUNTIF(D11:D16,{1,2,3}))


--
Biff
Microsoft Excel MVP


"Brad Vogt" wrote in message
...
=COUNTIF(D11:D16,1)+COUNTIF(D11:D16,2)+COUNTIF(D11 :D16,3)

D11:D16 is the area that has the values for years in company. You may
want
to name that range by selecting that are, then click up in the name area,
then type years and hit enter. After doing that, you can just type years
instead of D11:D16 when entering the formula.

"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please
help~

Sumif or Sumproduct?
 

Glad that it helped!

"Helpless in Colorado" wrote:

Brad that worked!!! Thank you so much.

"Brad Vogt" wrote:

=COUNTIF(D11:D16,1)+COUNTIF(D11:D16,2)+COUNTIF(D11 :D16,3)

D11:D16 is the area that has the values for years in company. You may want
to name that range by selecting that are, then click up in the name area,
then type years and hit enter. After doing that, you can just type years
instead of D11:D16 when entering the formula.

"Helpless in Colorado" wrote:

Hello,

If I have 3 columns:
NAME LOCATION YEARS IN COMPANY
Doe, John Franktown 1
Doe, Joan Franktown 2
Jones, Jessica Jacobs 2
Bradley, John Jacobs 3
Haften, Jose Kenton 3
Justin, Jasmine Liberty 4
Prok, Tealer Liberty NP

I want to to add the number of 1st, 2nd and 3rd year employees (only) for
each location. What formula do I use? I am currently only able to add 2
criteria to one forumla: =SUMPRODUCT(((F4:F25)="1st")+((F4:F25)="2nd")).
When I try to add another criterion, "3rd" I receive an error. Please help~