sumproduct
 

I have read all the posts on sumproduct and have finally managed to get the
formula to work. What I was doing wrong was asking the answer to be placed on
a different worksheet from the table it was pulling information from. Is
there any way to create a formula that will put the information on a
different worksheet?

Background on my formula:
I have 3 columns
Date Days absent City

I want to sum the days absent on a specific day based on a specific city.
This is my formula which works if placed in the same spreadsheet as the table
pulling info from.

{=SUMPRODUCT(--(A1:A600=J6)*(E1:E600="<city name"),D1:D600)}

My reason for putting the information in another worksheet is because I want
to be able to create a trend chart that shows more than just days absent by
city. The original worksheet actually contains about 12 columns of data
(alothough I only need the 4 of the 12 for the trend charting). So it is
quite cumbersome to keep everything on one worksheet.

Any information is most appreciated.

sumproduct
 

If J6 is in current sheet
=SUMPRODUCT(--(Sheet1!A1:A600=J6)*(Sheet1!E1:E600="city"),Sheet1 !D1:D600)

If this post helps click Yes
---------------
Jacob Skaria


"Corinne H" wrote:

I have read all the posts on sumproduct and have finally managed to get the
formula to work. What I was doing wrong was asking the answer to be placed on
a different worksheet from the table it was pulling information from. Is
there any way to create a formula that will put the information on a
different worksheet?

Background on my formula:
I have 3 columns
Date Days absent City

I want to sum the days absent on a specific day based on a specific city.
This is my formula which works if placed in the same spreadsheet as the table
pulling info from.

{=SUMPRODUCT(--(A1:A600=J6)*(E1:E600="<city name"),D1:D600)}

My reason for putting the information in another worksheet is because I want
to be able to create a trend chart that shows more than just days absent by
city. The original worksheet actually contains about 12 columns of data
(alothough I only need the 4 of the 12 for the trend charting). So it is
quite cumbersome to keep everything on one worksheet.

Any information is most appreciated.

sumproduct
 

{=SUMPRODUCT(--(A1:A600=J6)*(E1:E600="<city name"),D1:D600)}

Assuming the data is on Sheet1:

=SUMPRODUCT(--('Sheet1'!A1:A600=J6),--('Sheet1'!E1:E600="<city
name"),'Sheet1'!D1:D600)

You do not need to array enter this formula. Just a regular ENTER will do.

Also, it's be better to use a cell to hold the city name variable:

=SUMPRODUCT(--('Sheet1'!A1:A600=J6),--('Sheet1'!E1:E600=J7),'Sheet1'!D1:D600)

--
Biff
Microsoft Excel MVP


"Corinne H" <Corinne wrote in message
...
I have read all the posts on sumproduct and have finally managed to get the
formula to work. What I was doing wrong was asking the answer to be placed
on
a different worksheet from the table it was pulling information from. Is
there any way to create a formula that will put the information on a
different worksheet?

Background on my formula:
I have 3 columns
Date Days absent City

I want to sum the days absent on a specific day based on a specific city.
This is my formula which works if placed in the same spreadsheet as the
table
pulling info from.

{=SUMPRODUCT(--(A1:A600=J6)*(E1:E600="<city name"),D1:D600)}

My reason for putting the information in another worksheet is because I
want
to be able to create a trend chart that shows more than just days absent
by
city. The original worksheet actually contains about 12 columns of data
(alothough I only need the 4 of the 12 for the trend charting). So it is
quite cumbersome to keep everything on one worksheet.

Any information is most appreciated.

sumproduct
 

Assuming J6 is in Sheet2, with the other data in Sheet1, then put this
in an appropriate cell in Sheet2:

=SUMPRODUCT((Sheet1!A$1:A$600=J6)*(Sheet1!E$1:E$60 0="<city
name"),Sheet1!D$1:D$600)

I've put $ symbols in so that you can copy it down to pick up values
in J7, J8 etc.

Hope this helps.

Pete

On Jul 31, 6:59*pm, Corinne H <Corinne
wrote:
I have read all the posts on sumproduct and have finally managed to get the
formula to work. What I was doing wrong was asking the answer to be placed on
a different worksheet from the table it was pulling information from. Is
there any way to create a formula that will put the information on a
different worksheet?

Background on my formula:
I have 3 columns
Date * * Days absent * * City

I want to sum the days absent on a specific day based on a specific city.
This is my formula which works if placed in the same spreadsheet as the table
pulling info from.

{=SUMPRODUCT(--(A1:A600=J6)*(E1:E600="<city name"),D1:D600)}

My reason for putting the information in another worksheet is because I want
to be able to create a trend chart that shows more than just days absent by
city. The original worksheet actually contains about 12 columns of data
(alothough I only need the 4 of the 12 for the trend charting). So it is
quite cumbersome to keep everything on one worksheet.

Any information is most appreciated.