Hi all, please help, my excel skills are pretty limited. I need this formula in each cell in a table on sheet 2 but with a slight difference for each cell, so cell (sheet2 C1) would be

=SUMPRODUCT((Sheet1!D4:D27=Sheet1!B29)*(Sheet1!B4:B27=Sheet2!B1))

and cell (sheet2 C2) would be

=SUMPRODUCT((Sheet1!D4:D27=Sheet1!B29)*(Sheet1!B4:B27=Sheet2!B2)) etc etc

Thanks in advance

You should look up the differences between Absoloute and Relative references in the Excel Help files and/or Google. Place this in C1 and drag it down:

=SUMPRODUCT((Sheet1!$D$4:$D$27=Sheet1!$B$29)*(Sheet1!$B$4:$B$27=Sheet2!B1))

The ranges with the $ are Absolute References and will not change if you drag the formula down or across.

The range without the $ (B1) is a relative address and will change as you drag the formula around.

You can highlight the range in a formula and press F4 to cycle through the 4 referencing choices.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History