Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of every time 2 different texts are found in 2 different colum
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of every time 2 different texts are found in 2 different colum
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 "Heather" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colum is not found thats missing a column B how to find it | Excel Worksheet Functions | |||
Sumif a date time colum and information | Excel Discussion (Misc queries) | |||
Look up a value in one colum and insert the value found in the adj | New Users to Excel | |||
Keeping a sum colum correct after inserting a colum of data in fro | Excel Discussion (Misc queries) | |||
Check data on colum A and find match on colum b | Excel Discussion (Misc queries) |