How to sum non-contiguous columns applied as a formula on contiguous cells

Hello people:

I have the following scenario:

I would like to sum Sheet#1īs "Rec" "Ans" and "Aband" columns for each day ( October 7th,8th, etc) on Sheet #2, but Sheet #1īs columns are non-contiguous for the categories just described:

I am attaching an excel file as an example.


Thanks very much!!

Hello
In cell C2 copied across and down to cell ND4 on sheet2:

Please Login or Register  to view this content.

'Data' is a named range = B3:APG15 on Sheet1.

'Dates' is a named range = B1:APE1 on Sheet1.

I've done this for ease of reference but you could just reference the ranges. There are however, some date discrepancies with the years on the two sheets, so it's returning some #N/A errors. For example the date 28/5 on Sheet1 is 2012 but on Sheet2 it's 2014.

Hope this helps.
DBY

Thanks mate, I entered your formula on Sheet 2īs C2 cell but it returns #NAME? error, is there something that maybe I am missing? Sorry I am not that skilled on Excel!

Thanks DBY!

Originally Posted by Pepe Le Mokko

Have you named the ranges as required ? If not replace the names with the ranges

Thanks mate, well that is the part that I am missing, how can I name the ranges on sheet #1? I thought that ranges were already named by DBY on the formula.

Thanks!

Originally Posted by FDibbins

Hi and welcome to the forum

Try this, copied down and across...
=SUM(OFFSET(Sheet1!$B$1,2,MATCH(Sheet2!C$1,Sheet1!$B$1:$APG$1,0)+ROW(A1)-2,COUNT(Sheet1!$A:$A),1))

However, I would suggest that you swing your table around so that your dates go downwards instead of cross. Its always better to grow a table downwards rather than across

Thanks FDibbins !! It worked!!! Thanks for your advice on dates direction as well!

Thanks as well to DBY and Pepe Le Mokko!

If using my suggestion, select the range of cell as I've noted above and in the Name Box to the left of the Formula Bar Type in your name and press enter. Or just directly reference the ranges without the names.

DBY

Originally Posted by DBY

If using my suggestion, select the range of cell as I've noted above and in the Name Box to the left of the Formula Bar Type in your name and press enter. Or just directly reference the ranges without the names.

DBY

Thanks mate!!

FDibbins I will mark thread as solved thanks!!