| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formulas that compare and sum between worksheets
I need a formula to look at column AF to compare the date, then look at
column N for the work "rejected" then sum the total rejected's between 3 spreadsheets, and enter the output on sheet 5. I have one formula so far, but can't seem to figure how to add the comparison for the date: =COUNTIF(Projects!N:N,"REJECTED")+COUNTIF(AR!N:N," REJECTED")+COUNTIF(Legal!N:N,"REJECTED") |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formulas that compare and sum between worksheets
Hi,
which sheet name is the date on, and you want to compare the dates with ?? "Pat" wrote: I need a formula to look at column AF to compare the date, then look at column N for the work "rejected" then sum the total rejected's between 3 spreadsheets, and enter the output on sheet 5. I have one formula so far, but can't seem to figure how to add the comparison for the date: =COUNTIF(Projects!N:N,"REJECTED")+COUNTIF(AR!N:N," REJECTED")+COUNTIF(Legal!N:N,"REJECTED") |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formulas that compare and sum between worksheets
The date is on all sheets, just ion different columns.
Projects -date in column AA AR- -date in column AA Legal -date in column AB I want to look at those columns, compare the numeric month and then look in column N for the word rejected, total/sum the number based on the numeric month. "Eduardo" wrote: Hi, which sheet name is the date on, and you want to compare the dates with ?? "Pat" wrote: I need a formula to look at column AF to compare the date, then look at column N for the work "rejected" then sum the total rejected's between 3 spreadsheets, and enter the output on sheet 5. I have one formula so far, but can't seem to figure how to add the comparison for the date: =COUNTIF(Projects!N:N,"REJECTED")+COUNTIF(AR!N:N," REJECTED")+COUNTIF(Legal!N:N,"REJECTED") |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formulas that compare and sum between worksheets
OK try this
=SUMPRODUCT(--(MONTH(projects!A:A)=B1),--(Projects!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(AR!A:A)=B1),--(AR!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(Legal!A:A)=B1),--(Legal!N:N,"REJECTED)) in a summary sheet enter the month numbers from 1 to 12 starting in B1, you copy the above formula there and then copy it down "Pat" wrote: The date is on all sheets, just ion different columns. Projects -date in column AA AR- -date in column AA Legal -date in column AB I want to look at those columns, compare the numeric month and then look in column N for the word rejected, total/sum the number based on the numeric month. "Eduardo" wrote: Hi, which sheet name is the date on, and you want to compare the dates with ?? "Pat" wrote: I need a formula to look at column AF to compare the date, then look at column N for the work "rejected" then sum the total rejected's between 3 spreadsheets, and enter the output on sheet 5. I have one formula so far, but can't seem to figure how to add the comparison for the date: =COUNTIF(Projects!N:N,"REJECTED")+COUNTIF(AR!N:N," REJECTED")+COUNTIF(Legal!N:N,"REJECTED") |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formulas that compare and sum between worksheets
sorry what type of summary sheet? A seperate worksheet?
"Eduardo" wrote: OK try this =SUMPRODUCT(--(MONTH(projects!A:A)=B1),--(Projects!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(AR!A:A)=B1),--(AR!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(Legal!A:A)=B1),--(Legal!N:N,"REJECTED)) in a summary sheet enter the month numbers from 1 to 12 starting in B1, you copy the above formula there and then copy it down "Pat" wrote: The date is on all sheets, just ion different columns. Projects -date in column AA AR- -date in column AA Legal -date in column AB I want to look at those columns, compare the numeric month and then look in column N for the word rejected, total/sum the number based on the numeric month. "Eduardo" wrote: Hi, which sheet name is the date on, and you want to compare the dates with ?? "Pat" wrote: I need a formula to look at column AF to compare the date, then look at column N for the work "rejected" then sum the total rejected's between 3 spreadsheets, and enter the output on sheet 5. I have one formula so far, but can't seem to figure how to add the comparison for the date: =COUNTIF(Projects!N:N,"REJECTED")+COUNTIF(AR!N:N," REJECTED")+COUNTIF(Legal!N:N,"REJECTED") |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formulas that compare and sum between worksheets
Hi Pat,
it could be a separate sheet or you can use one of your actual sheets and enter the month numbers where you would like to see the information "Pat" wrote: sorry what type of summary sheet? A seperate worksheet? "Eduardo" wrote: OK try this =SUMPRODUCT(--(MONTH(projects!A:A)=B1),--(Projects!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(AR!A:A)=B1),--(AR!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(Legal!A:A)=B1),--(Legal!N:N,"REJECTED)) in a summary sheet enter the month numbers from 1 to 12 starting in B1, you copy the above formula there and then copy it down "Pat" wrote: The date is on all sheets, just ion different columns. Projects -date in column AA AR- -date in column AA Legal -date in column AB I want to look at those columns, compare the numeric month and then look in column N for the word rejected, total/sum the number based on the numeric month. "Eduardo" wrote: Hi, which sheet name is the date on, and you want to compare the dates with ?? "Pat" wrote: I need a formula to look at column AF to compare the date, then look at column N for the work "rejected" then sum the total rejected's between 3 spreadsheets, and enter the output on sheet 5. I have one formula so far, but can't seem to figure how to add the comparison for the date: =COUNTIF(Projects!N:N,"REJECTED")+COUNTIF(AR!N:N," REJECTED")+COUNTIF(Legal!N:N,"REJECTED") |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formulas that compare and sum between worksheets
when I enter the formula, I get the error message that the
"Eduardo" wrote: Hi Pat, it could be a separate sheet or you can use one of your actual sheets and enter the month numbers where you would like to see the information "Pat" wrote: sorry what type of summary sheet? A seperate worksheet? "Eduardo" wrote: OK try this =SUMPRODUCT(--(MONTH(projects!A:A)=B1),--(Projects!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(AR!A:A)=B1),--(AR!N:N,"REJECTED))+SUMPRODUCT(--(MONTH(Legal!A:A)=B1),--(Legal!N:N,"REJECTED)) in a summary sheet enter the month numbers from 1 to 12 starting in B1, you copy the above formula there and then copy it down "Pat" wrote: The date is on all sheets, just ion different columns. Projects -date in column AA AR- -date in column AA Legal -date in column AB I want to look at those columns, compare the numeric month and then look in column N for the word rejected, total/sum the number based on the numeric month. "Eduardo" wrote: Hi, which sheet name is the date on, and you want to compare the dates with ?? "Pat" wrote: I need a formula to look at column AF to compare the date, then look at column N for the work "rejected" then sum the total rejected's between 3 spreadsheets, and enter the output on sheet 5. I have one formula so far, but can't seem to figure how to add the comparison for the date: =COUNTIF(Projects!N:N,"REJECTED")+COUNTIF(AR!N:N," REJECTED")+COUNTIF(Legal!N:N,"REJECTED") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare worksheets | Excel Discussion (Misc queries) | |||
| Need to compare 2 worksheets... | Excel Discussion (Misc queries) | |||
| Compare two worksheets | Excel Worksheet Functions | |||
| compare worksheets | Excel Discussion (Misc queries) | |||
| Compare two worksheets | New Users to Excel | |||
Linear Mode
