sumif based on date and copy to another sheet

[Ali Khan]

Hi,

I have 2 worksheet "Product" and "Summary". Product sheet shows the due date in Column A & due and balance remaining amount in other columns. I can't provide column nos as no of products can vary.
Summary Sheet shows the sum of prouct balance remaining (from Product sheet) based on date in column B1 in summary sheet.

I want to copy the column headers in row 5 of summary sheet where '_remain' word appears in Product sheet . In next row provide their sum based on date in B1 of Summary sheet.

I have attached the file.

Thanks,
Khan

 

[Khalid NGO]

Hi @Ali Khan,

For headers contains "remain", Try this {array formula} in Summary sheet A5:
=INDEX(Product!$A$1:$Z$1,SMALL(IF(IFERROR(SEARCH("*remain",Product!$A$1:$Z$1),0)>0,COLUMN(Product!$A$1:$Z$1)),COLUMN(A1)))
Enter with Ctrl+Shift+Enter


this in A6 (with just enter) :
=SUMPRODUCT((Product!$A$2:$A$2000<=$B$1)*(Product!$B$1:$Z$1=A$5)*(Product!$B$2:$Z$2000))
Copy across both...
[adjust the range accordingly]

You will get error if your header does not contain the word "remain", if this is limited or just one go type of work, I suggest to type the "Total Balance Amt" and "Paid Amount" manually at the end; else you may need vba.

Additionally, you may wait for someone to came up with more suitable solution.

Regards,

 

[Ali Khan]

Thanks Khalid for your help. It's really useful and i learned one new thing.
I will try to use this formula in vba though i am at beginner stage.

Best Regards,
Ali Khan

 

[Khalid NGO]

Thanks Khalid for your help. It's really useful and i learned one new thing.

Welcome dear,

keep visiting, keep learning.
This we do here all the day