Need Help with SUMPRODUCT

[ashish514]

I need a little help with the sumproduct formula. I have this data which gives the travelling patterns of people living in certain city. For each trip, all the modes they use are tabled in separate columns in the order of use, also there is a column for the purpose of travel which are work/education/others.

Following is the link to the excel file which contains the example of the data table on sheet 1 and the table required to be made on sheet2

http://www.box.net/shared/2h69t3hz3l

I want to know how many times each mode is used for education/work/other purposes

For example, if I want to know how many work purpose trips consist of a train journey i am using this formula

=sumproduct((Sheet1!D4:D23="work")*(Sheet1!E4:H23=1)) ; where 1 is the code for train

But this formula will give me the required value only if train is not used more than one time in a single trip. While I want that that if in a single trip one mode of transport is used twice, It should be counted only once for that trip. Please help me on this

 

[arkusM]

I need a little help with the sumproduct formula. I have this data which gives the travelling patterns of people living in certain city. For each trip, all the modes they use are tabled in separate columns in the order of use, also there is a column for the purpose of travel which are work/education/others.

Following is the link to the excel file which contains the example of the data table on sheet 1 and the table required to be made on sheet2

http://www.box.net/shared/2h69t3hz3l

I want to know how many times each mode is used for education/work/other purposes

For example, if I want to know how many work purpose trips consist of a train journey i am using this formula

=sumproduct((Sheet1!D4:D23="work")*(Sheet1!E4:H23=1)) ; where 1 is the code for train

But this formula will give me the required value only if train is not used more than one time in a single trip. While I want that that if in a single trip one mode of transport is used twice, It should be counted only once for that trip. Please help me on this

I think you just need to tell the formula what column to t count,=sumproduct((Sheet1!D4:D23="work")*(Sheet1!E4:H23=1),Sheet1!E4:H23=1)

Should do the trick. Also it is a good idea to absolute the references so the arrays don't blow-out.

 

[ashish514]

@arkusm

The formula you gave is returning value 0. I am sure i am doing something wrong. Pls help

And by the way the link i gave earlier was for some other file, is sent it by mistake. This is the correct link of the excel file i was referring to:

http://www.box.net/shared/ck3if1aiur

 

[arkusM]

sumproduct((Sheet1!D4:D23="work")*(Sheet1!E4:H23=1),Sheet1!E4:H23=1<THIS there!! been have not should>)

The link you posted goes to a blank example. But there was typo in the formula I posted. the last argument should not have had the "=1" on the end. Sincere apologies.

=sumproduct((Sheet1!D4:D23="work")*(Sheet1!E4:H23=1),[B][COLOR=royalblue]Sheet1!E4:H23[/COLOR][/B])