Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare lists for additions and subtractions
At the start of each month I retrieve a list of 100+ names of people
currently subscribing to a group. I compare this list to the one retrieved last month. I can easily count the number in the list and the number increase or decrease. I'd like to calculate the following: 1) New - The number of people not on last month but new to this month's list. 2) Left List - The number of people on last month's list but not on this month's list. 3) On Both - The number of people on both lists. I assume these would be some sort of array formulas but I'm not quite sure how to get started. Thanks for the help. - John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare lists for additions and subtractions
Join
=SUMPRODUCT(--(COUNTIF(LastMonth,ThisMonth)=0)) Quit =SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=0)) Continue =SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=1)) Using Advanced Filter and these formulas can give you the list of names instead of just the count. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare lists for additions and subtractions
Very cool. Thanks, Herbert.
I modified your formula a bit so that I could copy it across my sheet for the various months. Since the size of the array for LastMonth and ThisMonth are not always the same and I don't have each month of data named, I used an offset formula to figure out the appropriate range. For Join I modifed your formula like this: =SUMPRODUCT(--(COUNTIF(OFFSET(C$8,0,0,C$2,1),OFFSET(D$8,0,0,D$2, 1))=0)) Whe C$8 is the top of the LastMonth Range D$8 the top of ThisMonth C$2 = total number in LastMonth D$2 = total number in ThisMonth Thanks for the help. - John Herbert Seidenberg wrote: Join =SUMPRODUCT(--(COUNTIF(LastMonth,ThisMonth)=0)) Quit =SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=0)) Continue =SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=1)) Using Advanced Filter and these formulas can give you the list of names instead of just the count. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|