How to total all numbers for a date (Row) when there are several columns with same name

Help! I have an extremely large and complex Excel spreadsheet with hundreds of columns and rows. The rows are dates, and the columns are item names.

In very simple terms, the spreadsheet looks like this: (Note the problem- more than one column has the same name). Also, both the dates and product titles might change. For example Apples may become Pears, and Pears may become Apples. I want the formula's output to take this into account. In other words, if I rename "Pears" to "Apples" the answer will become 83 instead of 53

Date Apples Pears Apples
01.05.21 2 15 33
10.07.21 3 30 50
15.07.21 6 40 210

Choose Date 11.07.21
Choose Product Apples
Total Sales 53

I just want a formula to give me the answer 53 in the example above. Seems easy? Not for me, and I am really good with Excel..
I have tried all kinds of combinations of Index, xMatch, xlookup, sum, sumif and sumifs, and so on, but I just can't make it work. I could potentially make it work using the "=IF" formula, but that would result in hundreds of nested "IF"s, which is probably more that I, and Excel, want to handle.

This seems at first sight to be such an easy problem to solve, but I have spent hours and can't figure out an easy solution .

Screenshot 2021-07-10 at 12.46.09.png

Like this:

=SUMPRODUCT(($A$5:$A$7=H5)*($B$4:$D$4=H6)*$B$5:$D$7)

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Hi,

We generally prefer a representative copy of your workbook in whch you have manually entered the results you expect and clearly explain your calculation.

From your description you seem to be picking the latest date prior to (or perhaps equal to) your stated date of 11/7/2021. You need to be clear on this and why not the sum of all previous dates.

Perhaps

=SUMIFS(OFFSET(B1:D1,MATCH(A7,A1:A4)-1,0,1,3),$B$1:$D$1,A8)

Where A1:D4 is your data
A7: 11/7/2021
A8: Apples

But first you'll need to ensure your column A dates are true date numbers and not just strings of text that look to you like a date.

It's very useful and versatile... but can also be a bit temperamental, for example when there is text present in a range where it expects a number...

<= date... I didn't see that first time round. I'd tend to avoid OFFSET, as it's volatile and recalculates every time anything changes. I'd use this instead:

=SUMPRODUCT(($A$5:$A$7=INDEX($A$5:$A$7,MATCH(H5,$A$5:$A$7,1)))*($B$4:$D$4=H6)*$B$5:$D$7)

Originally Posted by TRICKYT57

Thank you very much Richard. That is very useful. I have tried it (combining SUMIFS with OFFSET and MATCH and it works for me too, just like Glenn's answer above using SUMPRODUCT. I will practice with both answers, I think they will both work, except that I will need to modify something in Glenn's answer if my date does not match exactly one in the table. In your case a non-matched date takes the earliest prior date - which is what I really want.

Stating the obvious but it would be even easier if your data was collected in a regular and normalised 2 dimensional table. In your case columns for Date, Fruit, and Number. You could then avoid functions altogether and use a Pivot Table, or of course simplified functions.

It happens a lot where a bad design for data collection means hoops have to be jumped through to then summarise or analyse data. Better to get the database correct in the first place. It's always easier to report from a structured data set than complicate stuff by not having one.

Originally Posted by TRICKYT57

Glenn and Richard,

Thank you both. Glenn, I see how the use of Index and Match can be used to find the nearest earlier date. It’s just the solution I need.

Richard, I am struggling to understand what you meant by better layout of my data. I love pivot tables and use them all the time. However I don’t get what you mean by putting everything in columns. I am all for a better layout to make things easier.

Hi,

The essence of a normalised database range is that each column should be a UNIQUE TYPE of thing. In a PT when you want to summarise information about a single fruit then you just need to include the fruit column in the field list. You can't easily compare or summarise stuff in a PT when you have several columns (fields) that represent the same item.

Your layout has fruits as a type of thing, hence you should have a single column headed Fruits and record the type of fruit in that column. As a bonus typically you'll have less white space in a database.

I've moved your new post to its own thread here: https://www.excelforum.com/excel-for...-same-nam.html

Please go there and update the title to reflect the new request.