Excel Formula help

Closed
itswin - Mar 4, 2010 at 03:46 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 4, 2010 at 05:30 AM
Hello,
I have a workbook that contains a data sheet and a summary sheet. the data might look something like below
lady box
lady dice
lady box
man car
man car
man dice
child dice
child dice
child car

The summary sheet then looks like this
box dice car
lady
man
child

Is there a formula i can use that will total the number of times box appears alongside lady, dice appears alongside man etc.
I've been trying a few things but nothing seems to work. Any ideas would be most appreciated.

Win

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Mar 4, 2010 at 05:30 AM
you can do advance filter
suppose in sheet 1 the data is like this (row 1 colum headings-necessary)
person item
lady box
lady dice
lady box
man car
man car
man dice
child dice
child dice
child car

got ot sheet 2 and highlight A1
ciick data(menu bar)-filter -advance filter
in the advance filter window
slect radio button at the top "copy to another location"
list range is A1:
Sheet1!$A$1:$A$10
if you do not want to copy above click the small window at the end of the line "list range is" and take the mouse to sheet 1 column A a1 to A10 and hit enter key
crietira range leave it blank
copy to :$A$1
as you are in sheet 2 take the cursor to A1 and hit enter key
click the radio button at the bottom "unique record only)

shee 2 aform A1 will be

person
lady
man
child

regarding 2nd para
you can add in sheet 2 like this
a1 to D4

person box dice car
lady
man
child

the unique items like box, dice and car can also be enterered in B2 to D2 using advnace filter in columns away e.g. K1 and copy that list and paste special in B1 and clicking "translpose" in pastespecial window. You can take it as an exercise.

in B2copy paste this formula

=SUMPRODUCT((Sheet1!$A$1:$A$10=$A2)*(Sheet1!$B$1:$B$10=B$1))
(take care of dollar signs)
now copy B2 to the right to D2 and then upto row no 4
0