Sumif or Sumproduct?

  • Thread starter Helpless in Colorado
  • Start date
H

Helpless in Colorado

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~
 
T

Tyro

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(range,"3")

Tyro
 
B

Brad Vogt

=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.
 
B

Brad Vogt

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

Helpless in Colorado

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.
 
H

Helpless in Colorado

Brad that worked!!! Thank you so much.

Brad Vogt said:
=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.
Click to expand...
 
H

Helpless in Colorado

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.
 
H

Helpless in Colorado

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

Tyro

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

Tyro
 
F

FLKulchar

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
 
Top