Hi All, I am looking for a little bot of excel help. I have a table that contains data like so
Bob, 01/01/2016
bob, 01/01/2016
bob, 05/05/2016
jim, 01/05/2016
terry, 06/09/2016
etc
I want to sum the values in another table that has every date of the year on it.
For example, under bob and 01/01/2016 you would have '2'
excel wizards
-
BiggestNizzy
- Posts: 8932
- Joined: Sun May 27, 2007 6:47 pm
- Location: Kilmarnock
- Contact:
excel wizards
Sent from my ZX SPECTRUM +2A
Re: excel wizards
COUNTIFS will do this for you.
Assuming you've got your names in on Sheet 1 in Column A and your dates in Column B, create another table on Sheet 2 with Dates from A2 downwards and Names from B1 across the way.
In B2 put the formula =COUNTIFS(Sheet1!$A$1:$A$300,B$1,Sheet1!$B$1:$B$300,$A2) then just drag it across all name columns and down all the dates. Obviously if you have more than 300 names and dates in the source data table you'll need to expand the 300 range I've put.
Assuming you've got your names in on Sheet 1 in Column A and your dates in Column B, create another table on Sheet 2 with Dates from A2 downwards and Names from B1 across the way.
In B2 put the formula =COUNTIFS(Sheet1!$A$1:$A$300,B$1,Sheet1!$B$1:$B$300,$A2) then just drag it across all name columns and down all the dates. Obviously if you have more than 300 names and dates in the source data table you'll need to expand the 300 range I've put.
Exige V6