SUMIf

I have a worksheet titled lost days that look like this:

A B C D
1 Name 96-97 97-98 98-99
2 Bert
3 oscar
4 elmo
5 elmo
6 maria


Then a 96-97 worksheet (i have one for each year begining 96 -2004) with :

A B C D
1 Name Lost time Age Gender
2 elmo 2
3 elmo 0
4 Bert 1
5 Bert 36
6 ernie 0
7 big bird 0
8 oskar 0

I want to sum the total of lost days per person from the yearly worksheets
and enter it under the respective year int he lost days worksheet. For
example, I want know who may days did Bert lose in 96-97. I I thought I
could use sumif, but I'm the problem is I don't know how to tell it to sum
the colum B of rows that where it found the name Bert. I know $ make it
always do colum B, but what symbol do I use to say "from the rows the
correspond with the name (criteria)"? For Bert the formula would be:

=SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B4:$B5)

That would not work for anyone else as the row for their data are different

=SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B?:$B?).

Help

If I understand correctly, it seems like you're trying to do the work for
Excel by identifying which rows to add up. But that's exactly what the sumif
function can do for you.
Try =SUMIF('96-97'!$A:$A,'Lost days'!$A2,'96-97'!$B:$B)
That says to look for values that match 'Lost days'$A2 in all of column A on
sheet '96-97' and, where found, add the corresponding values from column B.
So wherever you see Bert in column A, add the values from column B.
--Bruce


"Sojo" wrote:

> I have a worksheet titled lost days that look like this:
>
> A B C D
> 1 Name 96-97 97-98 98-99
> 2 Bert
> 3 oscar
> 4 elmo
> 5 elmo
> 6 maria
>
>
> Then a 96-97 worksheet (i have one for each year begining 96 -2004) with :
>
> A B C D
> 1 Name Lost time Age Gender
> 2 elmo 2
> 3 elmo 0
> 4 Bert 1
> 5 Bert 36
> 6 ernie 0
> 7 big bird 0
> 8 oskar 0
>
> I want to sum the total of lost days per person from the yearly worksheets
> and enter it under the respective year int he lost days worksheet. For
> example, I want know who may days did Bert lose in 96-97. I I thought I
> could use sumif, but I'm the problem is I don't know how to tell it to sum
> the colum B of rows that where it found the name Bert. I know $ make it
> always do colum B, but what symbol do I use to say "from the rows the
> correspond with the name (criteria)"? For Bert the formula would be:
>
> =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B4:$B5)
>
> That would not work for anyone else as the row for their data are different
>
> =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B?:$B?).
>
> Help

Thanks a million, that worked! However, now I have another problem. I need
to distinguish between values that were and weren't matched. How do I tell it
to put a a blank (" ") for the values that do not match 'Lost days'!$A2 in
all of column A on sheet '96-97'. I tried this on the actual data and it
just makes everything blank:

IF(B2='96-97 SIF'!$B:$B,SUMIF('96-97 SIF'!$B:$B,'Lost days spss'!$B2,'96-97
SIF'!$O:$O)," ")

"bpeltzer" wrote:

> If I understand correctly, it seems like you're trying to do the work for
> Excel by identifying which rows to add up. But that's exactly what the sumif
> function can do for you.
> Try =SUMIF('96-97'!$A:$A,'Lost days'!$A2,'96-97'!$B:$B)
> That says to look for values that match 'Lost days'$A2 in all of column A on
> sheet '96-97' and, where found, add the corresponding values from column B.
> So wherever you see Bert in column A, add the values from column B.
> --Bruce
>
>
> "Sojo" wrote:
>
> > I have a worksheet titled lost days that look like this:
> >
> > A B C D
> > 1 Name 96-97 97-98 98-99
> > 2 Bert
> > 3 oscar
> > 4 elmo
> > 5 elmo
> > 6 maria
> >
> >
> > Then a 96-97 worksheet (i have one for each year begining 96 -2004) with :
> >
> > A B C D
> > 1 Name Lost time Age Gender
> > 2 elmo 2
> > 3 elmo 0
> > 4 Bert 1
> > 5 Bert 36
> > 6 ernie 0
> > 7 big bird 0
> > 8 oskar 0
> >
> > I want to sum the total of lost days per person from the yearly worksheets
> > and enter it under the respective year int he lost days worksheet. For
> > example, I want know who may days did Bert lose in 96-97. I I thought I
> > could use sumif, but I'm the problem is I don't know how to tell it to sum
> > the colum B of rows that where it found the name Bert. I know $ make it
> > always do colum B, but what symbol do I use to say "from the rows the
> > correspond with the name (criteria)"? For Bert the formula would be:
> >
> > =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B4:$B5)
> >
> > That would not work for anyone else as the row for their data are different
> >
> > =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B?:$B?).
> >
> > Help

The easiest way would be to have Excel supress 0 values: Tools > Options >
View, uncheck the 'Zero Values' box.
Of course, that no longer differentiates between a 0 and a non-match. If
that's important, you can test for the match and only perform the sumif when
the match finds the value on the other sheet:
=IF(ISNA(MATCH('Lost days'!A2,'96-97'!$A:$A,0)),"",SUMIF('96-97'!$A:$A,'Lost
days'!$A2,'96-97'!$B:$B))
In words: if the match returns an NA (that is, if the value isn't found),
return a null string. Otherwise, return the sumif result.
--Bruce


"Sojo" wrote:

> Thanks a million, that worked! However, now I have another problem. I need
> to distinguish between values that were and weren't matched. How do I tell it
> to put a a blank (" ") for the values that do not match 'Lost days'!$A2 in
> all of column A on sheet '96-97'. I tried this on the actual data and it
> just makes everything blank:
>
> IF(B2='96-97 SIF'!$B:$B,SUMIF('96-97 SIF'!$B:$B,'Lost days spss'!$B2,'96-97
> SIF'!$O:$O)," ")
>
> "bpeltzer" wrote:
>
> > If I understand correctly, it seems like you're trying to do the work for
> > Excel by identifying which rows to add up. But that's exactly what the sumif
> > function can do for you.
> > Try =SUMIF('96-97'!$A:$A,'Lost days'!$A2,'96-97'!$B:$B)
> > That says to look for values that match 'Lost days'$A2 in all of column A on
> > sheet '96-97' and, where found, add the corresponding values from column B.
> > So wherever you see Bert in column A, add the values from column B.
> > --Bruce
> >
> >
> > "Sojo" wrote:
> >
> > > I have a worksheet titled lost days that look like this:
> > >
> > > A B C D
> > > 1 Name 96-97 97-98 98-99
> > > 2 Bert
> > > 3 oscar
> > > 4 elmo
> > > 5 elmo
> > > 6 maria
> > >
> > >
> > > Then a 96-97 worksheet (i have one for each year begining 96 -2004) with :
> > >
> > > A B C D
> > > 1 Name Lost time Age Gender
> > > 2 elmo 2
> > > 3 elmo 0
> > > 4 Bert 1
> > > 5 Bert 36
> > > 6 ernie 0
> > > 7 big bird 0
> > > 8 oskar 0
> > >
> > > I want to sum the total of lost days per person from the yearly worksheets
> > > and enter it under the respective year int he lost days worksheet. For
> > > example, I want know who may days did Bert lose in 96-97. I I thought I
> > > could use sumif, but I'm the problem is I don't know how to tell it to sum
> > > the colum B of rows that where it found the name Bert. I know $ make it
> > > always do colum B, but what symbol do I use to say "from the rows the
> > > correspond with the name (criteria)"? For Bert the formula would be:
> > >
> > > =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B4:$B5)
> > >
> > > That would not work for anyone else as the row for their data are different
> > >
> > > =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B?:$B?).
> > >
> > > Help

Differentiating between 0 and non-match is importantt. The formula is
exactly what I needed. Thank you so much for your help. Stay blessed.

"bpeltzer" wrote:

> The easiest way would be to have Excel supress 0 values: Tools > Options >
> View, uncheck the 'Zero Values' box.
> Of course, that no longer differentiates between a 0 and a non-match. If
> that's important, you can test for the match and only perform the sumif when
> the match finds the value on the other sheet:
> =IF(ISNA(MATCH('Lost days'!A2,'96-97'!$A:$A,0)),"",SUMIF('96-97'!$A:$A,'Lost
> days'!$A2,'96-97'!$B:$B))
> In words: if the match returns an NA (that is, if the value isn't found),
> return a null string. Otherwise, return the sumif result.
> --Bruce
>
>
> "Sojo" wrote:
>
> > Thanks a million, that worked! However, now I have another problem. I need
> > to distinguish between values that were and weren't matched. How do I tell it
> > to put a a blank (" ") for the values that do not match 'Lost days'!$A2 in
> > all of column A on sheet '96-97'. I tried this on the actual data and it
> > just makes everything blank:
> >
> > IF(B2='96-97 SIF'!$B:$B,SUMIF('96-97 SIF'!$B:$B,'Lost days spss'!$B2,'96-97
> > SIF'!$O:$O)," ")
> >
> > "bpeltzer" wrote:
> >
> > > If I understand correctly, it seems like you're trying to do the work for
> > > Excel by identifying which rows to add up. But that's exactly what the sumif
> > > function can do for you.
> > > Try =SUMIF('96-97'!$A:$A,'Lost days'!$A2,'96-97'!$B:$B)
> > > That says to look for values that match 'Lost days'$A2 in all of column A on
> > > sheet '96-97' and, where found, add the corresponding values from column B.
> > > So wherever you see Bert in column A, add the values from column B.
> > > --Bruce
> > >
> > >
> > > "Sojo" wrote:
> > >
> > > > I have a worksheet titled lost days that look like this:
> > > >
> > > > A B C D
> > > > 1 Name 96-97 97-98 98-99
> > > > 2 Bert
> > > > 3 oscar
> > > > 4 elmo
> > > > 5 elmo
> > > > 6 maria
> > > >
> > > >
> > > > Then a 96-97 worksheet (i have one for each year begining 96 -2004) with :
> > > >
> > > > A B C D
> > > > 1 Name Lost time Age Gender
> > > > 2 elmo 2
> > > > 3 elmo 0
> > > > 4 Bert 1
> > > > 5 Bert 36
> > > > 6 ernie 0
> > > > 7 big bird 0
> > > > 8 oskar 0
> > > >
> > > > I want to sum the total of lost days per person from the yearly worksheets
> > > > and enter it under the respective year int he lost days worksheet. For
> > > > example, I want know who may days did Bert lose in 96-97. I I thought I
> > > > could use sumif, but I'm the problem is I don't know how to tell it to sum
> > > > the colum B of rows that where it found the name Bert. I know $ make it
> > > > always do colum B, but what symbol do I use to say "from the rows the
> > > > correspond with the name (criteria)"? For Bert the formula would be:
> > > >
> > > > =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B4:$B5)
> > > >
> > > > That would not work for anyone else as the row for their data are different
> > > >
> > > > =SUMIF('96-97'!A2:A36,'Lost days'!A2,'96-97 SIF'!$B?:$B?).
> > > >
> > > > Help