Text Calculations

[3587]

I have a spreadsheet that I want to keep track of the number of times that something occurs...

Column A
Dog
Cat
Fish

Column B
House
Work
Backyard

In the spreadsheet, I want to track the number of times that Dog+House occur together and add that number to a cell within the spreadsheet. What is the calculation that I would need for each cell?

Same goes for Dog+Work, or Dog+Backyard, or Cat+Work, etc.

 

[Weaver]

try

=sumproduct(--(A1:A100="cat"),--(B1:B100="house"))

You can enter this function normally (just press enter)

 

[3587]

Would it also help if I told you that this was for Excel 2003?

I'm sorry, but I don't follow I'm afraid. I thought you were after a count of each time a certain combination value occurred?

If it's something different, could you explain further?

Matty

 

[Weaver]

If you're not sure as to what combinations you need, you might like to try a pivot table.

1. Make sure each column has a header. I used "animal" for A and "location" for B
2. Select the columns and start the pivot table wizard.
Click until you get to the page of the wizard where there's a button labelled 'layout' in the bottom left. Click this.
3. Drag 'animal' to row source and location to column source
4. Drag either one to the 'data source' and make sure it's set to count
5. The grid of the thus formed table will count the combinations of each pair in the data.

 

[3587]

try

=sumproduct(--(A1:A100="cat"),--(B1:B100="house"))

You can enter this function normally (just press enter)

I think I got it to work, but I didn't use the "--", what exactly does that do? Also, does the "A100" mean that only the first 100 in that column will be counted?

Thanks!

 

[Weaver]

A100 - yes, only the first 100, but you can easily change this to suit.

If you're using 2007 or later you can use whole columns, not so with 2003

the -- turns true/false into 1/0 so that you can do maths on (i.e count) the results. This has been well documented all over this messageboard, so I'm a little surprised you're getting it to work without. Still, if it works...