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")

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")

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")

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")

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")

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")

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")