sumproduct

[Corinne H]

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>"),D1600)}

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.

 

[Jacob Skaria]

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

If this post helps click Yes

 

[T. Valko]

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

Assuming the data is on Sheet1:

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

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'!D1600)

 

[Pete_UK]

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$600="<city
name>"),Sheet1!D$1$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