Forum Discussion
How to sum columns and cells with dates and simple criteria?
I admit, I am not very skilled at excel. I want to sum numbers in column B IF they have a key word in column C, AND exist within certain dates from column A. I then want that SUM to appear in a specific cell. For example, I want to sum the numbers from column B that have the word "open" in column C, and have a date in column C between July 1st and 31st. I want that sum to appear in E2. I used the formula:
=SUMIFS(B:B, C:C,"*open*", A:A,">=7/01/2025", A:A, "<=7/31/2025") in E2. That works great, but when I copy the formula to F2 and try the same formula but change the dates to August it doesn't work. it just gives me 0. Also, when I paste or alter the formula to E3, for example, it pastes the formula to E3-E9.
Do you have any advice?
Thank you!
E
4 Replies
I've attached a demo with two solutions: pivot table, PIVOTBY.
If your date column is clean and not text then this is straightforward.
I do have some advice. It would begin with redesigning your workbook. Seriously. You've approached this in a way that forces YOU to do most of the heavy lifting, using Excel as you would work with paper ledger sheets. For example, in addition to needing to come up with a set of formulas to summarize each month's data by category or key word, it's clear that you have data for each of the last seven fiscal years, each year in its own tab. You could put all of those together in one large dataset (the dates are already year specific, so you don't need to separate them into their own sheets/tabs).
IF you were to assemble all your data into a single database, then let Excel demonstrate its Pivot Table magic, it would do those month-by-month summaries of hours per column C categories. You wouldn't even need to write a formula. Excel would be doing ALL of the heavy lifting. Here's an introduction to Pivot Tables. There are also quite a few YouTube videos.
I've attached an example of how Pivot Tables can take a set of data with dates and other characteristics and summarize the data by month and category. Your data are comparable.
If this isn't a confidential workbook, and if you are willing to post the actual workbook rather than an image, I'd be happy to modify it and get you started. And, seriously, that would be more helpful to you in the long run than fixing this formula.
Why don't you use a pivot table?
Thank you all for your help. I am going to learn a little more about pivot tables and will be back with more questions. Do Pivot tables work on the web version of Excel? I need it to work on the online Excel and couldn't get my VBA Macros to work properly on the web. Another question: IS there a way to have a blank row in the data columns so that when I enter the data and hit return, it sends the data down one row, and leaves the current row empty and ready for the next data to be input? Thank you all in advance.
