How to use SUMPRODUCT
Requirement:
have attached an excel with Sheet 1 & 2, where Sheet 1 have some data and in sheet 2 i have entered a formula in cell D7.
I need a formula to increase the Column number by dragging it to next cell,
For Eg: in Cell D7 i have formula =SUM('Sheet 1'!D4:D44) and if I drag it to D8 then it should be =SUM('Sheet 1'!E4:E44) and so on.
Sample Sheet 1:
Solution:
=SUMPRODUCT(('Sheet 1'!$D$1:$DJ$1=$C7)*('Sheet 1'!$C$4:$C$150=LEFT(D$6,3))*'Sheet 1'!$D$4:$DJ$150)
Obtained from the OzGrid Help Forum.
Solution provided by AliGW.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use IFERROR, ROUND and SUM in one formula |
| How to sum from two tables based on From-To date criteria |
| How to use SUMIF to extract attendance details from a register |
| How to use SUMIF using Variable Columns |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
