getting the sum based on column criteria

Hi,

Can anyone here help me with the formula to get the total summation based on the column criteria by date. I have attached a sample file.

On Raw file, there is a data where in I need to get the total Summation on each column based on the output tab.

For example, on column US400 for May 1, 2022 the total sum is 4601.

Hope can anyone has an idea? Thanks much!

Your dates in column A of the Raw sheet are in text format, so you need to convert them first. Select column A then click on Data | Text-to-columns | Next | Next then choose Date with MDY then click Finish.

Then you can use this formula in C2 of the Output sheet:

=SUMIFS(Raw!B:B,Raw!$A:$A,$A2)

Copy across to E2, then copy down as required.

Hope this helps.

Pete

thanks Pete_UK.

Also, can we make it dynamic? as some of the columns on raw data are moving. for example code, US400 is in column F and PTY is in column Y.

Thanks again

You can use this variation in C2:

=IFERROR(SUMIFS(INDEX(Raw!$B:$D,0,MATCH(TRIM(C$1),Raw!$B$1:$D$1,0)),Raw!$A:$A,$A2),"")

to match on the column headings. Change the ranges in red to suit your actual file, then copy across and down as required.

Hope this helps.

Pete