Counting in multiple column
I am struggling with a spreadsheet attempting to count events with criteria
in several columns. Column A - MMM-YY date (only month and year are keyed in) Column B - Reference Number (with blanks that have to be omitted from count) Column C - Equipment Description (contains concantenation of 3 other columns) I need to count only those lines where C meets one criterion, B is not blank and A is in the correct month. I hope to put each in a X:X range so the formulas will not have to be revised as new data is added. I tried SUMPRODUCT but I think my cell formatting may not be correct to achieve this. I also put in a cell range A1:A30 which did not solve the problem. I struggle with picking up the dates appropriately as well. The output I am seeking is: Count of only those cells where Column C matches the equipment description and Column B is not blank for the month required from Column A. Any help would be greatly appreciated. |
Counting in multiple column
You prb had trouble getting the month to match.
=SUMPRODUCT((month(A1:A30)=1)*(ISBLANK(B1:B30)=FAL SE)*(C1:C30="Truck")) You could change the 1 to any number up to 12, or reference another cell such as =MONTH(D1) "Truck" could of course also be replace with a cell reference. Hope that helps. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hope" wrote: I am struggling with a spreadsheet attempting to count events with criteria in several columns. Column A - MMM-YY date (only month and year are keyed in) Column B - Reference Number (with blanks that have to be omitted from count) Column C - Equipment Description (contains concantenation of 3 other columns) I need to count only those lines where C meets one criterion, B is not blank and A is in the correct month. I hope to put each in a X:X range so the formulas will not have to be revised as new data is added. I tried SUMPRODUCT but I think my cell formatting may not be correct to achieve this. I also put in a cell range A1:A30 which did not solve the problem. I struggle with picking up the dates appropriately as well. The output I am seeking is: Count of only those cells where Column C matches the equipment description and Column B is not blank for the month required from Column A. Any help would be greatly appreciated. |
Counting in multiple column
Luke,
Thank you so much. I knew I was having trouble getting the logic to match the date format. Great work-around!! Hope "Luke M" wrote: You prb had trouble getting the month to match. =SUMPRODUCT((month(A1:A30)=1)*(ISBLANK(B1:B30)=FAL SE)*(C1:C30="Truck")) You could change the 1 to any number up to 12, or reference another cell such as =MONTH(D1) "Truck" could of course also be replace with a cell reference. Hope that helps. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Hope" wrote: I am struggling with a spreadsheet attempting to count events with criteria in several columns. Column A - MMM-YY date (only month and year are keyed in) Column B - Reference Number (with blanks that have to be omitted from count) Column C - Equipment Description (contains concantenation of 3 other columns) I need to count only those lines where C meets one criterion, B is not blank and A is in the correct month. I hope to put each in a X:X range so the formulas will not have to be revised as new data is added. I tried SUMPRODUCT but I think my cell formatting may not be correct to achieve this. I also put in a cell range A1:A30 which did not solve the problem. I struggle with picking up the dates appropriately as well. The output I am seeking is: Count of only those cells where Column C matches the equipment description and Column B is not blank for the month required from Column A. Any help would be greatly appreciated. |
All times are GMT +1. The time now is 03:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com