SUMIF problem
I am trying to create a SUMIF formula (maybe nested) that will take the correct sum from a table of data that looks a little like below; Initials Week created No of entries AB 45 100 GH 46 80 I am trying to use this data to return a result into a summary table as below Week 45 46 Initials AB GH for the person to the left & for the week specified above sum the entries found in the data. Hope someone can help. -- Hodge ------------------------------------------------------------------------ Hodge's Profile: http://www.excelforum.com/member.php...o&userid=16342 View this thread: http://www.excelforum.com/showthread...hreadid=277308 |
One way ..
In Sheet1 ------------- Assume the table is in cols A to C, data from row2 down, i.e.: Initials WeekCreated NoOfEntries AB 45 100 GH 46 80 etc In Sheet2 ------------- With initials listed in A3 down, and week #s listed in B2 across, viz.: Week Initials 45 46 etc AB ? ? GH ? ? etc Put in B3: =SUMPRODUCT((Sheet1!$A$2:$A$1000=$A3)*(Sheet1!$B$2 :$B$1000=B$2),Sheet1!$C$2: $C$1000) Copy B3 across as required, then fill down to populate the table (Adjust the ranges to suit - these have to be identical, and note that entire col references [B:B, C:C, etc] cannot be used in SUMPRODUCT) For the sample data in Sheet1, you'll get: Week Initials 45 46 AB 100 0 GH 0 80 To have a cleaner look, you could suppress extraneous zeros from showing via: Tools Options View tab Uncheck Zero values OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Hodge" wrote in message ... I am trying to create a SUMIF formula (maybe nested) that will take the correct sum from a table of data that looks a little like below; Initials Week created No of entries AB 45 100 GH 46 80 I am trying to use this data to return a result into a summary table as below Week 45 46 Initials AB GH for the person to the left & for the week specified above sum the entries found in the data. Hope someone can help. -- Hodge ------------------------------------------------------------------------ Hodge's Profile: http://www.excelforum.com/member.php...o&userid=16342 View this thread: http://www.excelforum.com/showthread...hreadid=277308 |
All times are GMT +1. The time now is 08:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com