Difficult lookup,sum nesting formula

[notanexcelguy]

Marcol did an amazing amount of work. His work led me to where I needed to go! Much obliged!


Column B Column E Column F Column G Column H Column I Column J Column K
Name 7/8 7/15 7/22 7/29 8/5 8/19 8/26
Not2ndValue 40 40 40 40 40 40 40
2nd Value 40 40 40 40 40 40 40 40
Not2ndValue 40 40 40 40 40 40 40
2nd Value 40 40 40 40 40 40 40

So I want:
1. Search for all columns with dates w/in the month of July
2. Look at each value in the columns from Step 1, and find the values that have the "2nd Value" in the same row
3. Once all the values w/2nd Value are located, sum the values from the columns in July

Desired Formula Result from this table: 320

Unfortunately, I am building inside of someone else sheet full of macros and I cannot change the layout.

[notanexcelguy]

Attached a .gif of my sheet, will help with understanding the data layout. Sorry I am learning the ropes of posting.

[Marcol]

This is difficult to verify as all of your data is the same.

What is the relationship between "Not2ndValue" and "2nd Value"?
I have assumed they must be equal.

Try this workbook to see if it gets the ball rolling.

If this is not correct, post a sample workbook showing some typical data and the expected result.

Hope this helps

[Marcol]

A picture is not the best approach to this problem, post a sample workbook.

Fill in some data and show the expected result - a sheet full of zeros is as useless as a castor on a wooden leg!

[notanexcelguy]

I greatly appreciate the help and think we are almost there! I need it to be a bit more dynamic though.

So this time I attached the workbook.

I want to write formula to return a result for Sheet 2 B7

The formula should
1. look in all the data in Sheet 1
2. Find the columns that have Jan dates
3. run down the values in of the Jan date columns and look horizontally for "NCT - Bus Solutions-ATM Analyst"
4. Sum all the values that are true for Step 3

[notanexcelguy]

The example I gave only has the Platform Name appearing a single time in the range. There will be times when the Platform Name appears multiple times and the formula will need to find all the times it appears and sum those numbers

[notanexcelguy]

With expected result on Sheet 2 now

[Marcol]

Okay I had this done before I saw your last post.

Try this workbook.

I have added a sheet for lookups for data validation. These named lists are dynamic, add or subtract from these lists as required, but don't leave blanks. The lists must be continuous.

I have added the facility to calculate with or without "name" just leave A2 blank if you don't need to calculate individual names.
We can adjust the formula later if this is not required, or indeed if you also want the total per name regardless of platform.

Select from the drop-downs in A2:D2

I don't know if you need to allow for the year or only the month regardless of the year, my logic would be to calculate by both year and month.
This is easily fixed if I am wrong.

Note the formatting of the months in the sheet lookups, this is to allow for a number in the calcs, but display text in the data validation in C2.

The formula column would be better at the begining of the sheet to allow easier maintenance, but we can also look at that later.

See how this fits and post a reply, I have to go for a while, I'll look back later and if still required I'll look at your last posted workbook.

Hope this helps.

Alistair