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.

 

[Matty]

The following would count 1 for every occasion where Dog occurs in Column A and House appears in Column B, within the same Row:

=SUM(IF(A1:A3="Dog",IF(B1:B3="House",1)))

Which needs array entering, i.e. with CTRL+SHIFT+ENTER.

But I have a suspicion you want more than just this...

Matty

 

[3587]

Thanks. I'll try this and let you know...

Can you explain the "A1:A3" part?

The following would count 1 for every occasion where Dog occurs in Column A and House appears in Column B, within the same Row:

=SUM(IF(A1:A3="Dog",IF(B1:B3="House",1)))

Which needs array entering, i.e. with CTRL+SHIFT+ENTER.

But I have a suspicion you want more than just this...

Matty

 

[Matty]

A1:A3 is Dog, Cat, Fish, as per your example. B1:B3 is House, Work, Backyard.

Clearer now?

Matty

 

[3587]

What if I have a list of 50 items in Column A? Does that change anything?

What I eventually want to happen is for me to copy/paste a bunch of occurrences and have them self calculate into the spreadsheet cells.

A1:A3 is Dog, Cat, Fish, as per your example. B1:B3 is House, Work, Backyard.

Clearer now?

Matty

 

[Matty]

What if I have a list of 50 items in Column A? Does that change anything?

It changes nothing.

Make a list of the combinations of interest, say in Columns D and E, then use:

=SUM(IF(A$1:A$100=D1,IF(B$1:B$100=E1,1)))

Committed with CTRL+SHIFT+ENTER and copy down.

Change ranges to suit your real data set.

Matty

 

[3587]

But my data set is always changing... That is why on different cells of my spreadsheet I just need certain combination sets to go to that cell... From Column A and B of course... Maybe I just suck at explaining this

It changes nothing.

Make a list of the combinations of interest, say in Columns D and E, then use:

=SUM(IF(A$1:A$100=D1,IF(B$1:B$100=E1,1)))

Committed with CTRL+SHIFT+ENTER and copy down.

Change ranges to suit your real data set.

Matty

 

[Matty]

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

 

[3587]

I'll try both combos and let you know. Just the amount of times each combo happens will be more or less each month that gets pasted into Column A and B. But the location of each occurring incident remains in the same location on the spreadsheet.

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

 

[3587]

Neither of those calculations worked... I need Cat+House to enter "1", etc.

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