Sum of every time 2 different texts are found in 2 different colum

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I am regarding as fairly Excel smart at work, however by comparison to these
questions I am an idiot.

I have a running list which rows are added to nearly everyday. Columns A –
L are populated with different data. Specifically, column G has employee
names and column J has dates.

I have a summary page with all dates listed horizontally and all employee
names listed vertically. I would love it if, for example, on the summary
page under date (August 26th) and in the employee name row (Heather) the cell
would populate with the total number of times Heather and August 26th were in
the same row on the running list.

I hope this makes sense.

I appreciate any assistance.

Regards,
Heather
 
Heather- if your names are in Sheet1 Col G and your dates are in Sheet1 Col J

Create a list of unique names and dates on Sheet2, with dates in B1:?1
(however many you have) and put the names in A2:A? (however many you have).

Put this formula in Sheet2 Cell B2, then copy it across your whole range:
=SUMPRODUCT((B$1=Sheet1!$J$1:$J$1000)*1,(Sheet1!$G$1:$G$1000=Sheet2!$A2)*1)

Note that the placement of the dollar signs is intentional to allow it to
fill out your target range properly. If you have more than 1000 records on
Sheet 1, extend the range by changing the values of 1000 to a larger number.

If you don't want to show zero values, try something like:

=IF(SUMPRODUCT((B$1=Sheet1!$J$1:$J$1000)*1,(Sheet1!$G$1:$G$1000=Sheet2!$A2)*1)=0,"",SUMPRODUCT((B$1=Sheet1!$J$1:$J$1000)*1,(Sheet1!$G$1:$G$1000=Sheet2!$A2)*1))

Or much easier, just use conditional formatting to set the text color of
zero value cells to white (matches the background color)

HTH,
Keith
 
Back
Top