Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIf
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIf
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIf
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIf
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF function help | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions |