SUMPRODUCT uniques

Hi everyone,

Is it possible to have this formula work across multiple worksheets?, Without any addin.

=SUMPRODUCT(--(A3:A100="a"),--(MATCH(B3:B100&C3:C100,B3:B100&C3:C100,0)=ROW(INDEX(A3:A100,0))-ROW($A$3)+1),D3:D100)

Click to expand...

MATCH() needs a vector, so it cannot cope with a 3d result.

MATCH() needs a vector, so it cannot cope with a 3d result.

Click to expand...

How about using the FREQUENCY function? I think it cope with 3d results.

MATCH() needs a vector, so it cannot cope with a 3d result.

Click to expand...

How about using the FREQUENCY function? I think it cope with 3d results.

Click to expand...

What kind of data do you have in B and C ranges?

What kind of data do you have in B and C ranges?

Click to expand...

Numbers in both columns.

Here is a visual sense what I'm looking for. This formula below will look in column B & C and count the uniques. The result is 9.

=SUM(N(FREQUENCY(Sheet2:Sheet3!B2:C100,Sheet2:Sheet3!B2:C100)>0))

But what I need from the formula above is to look in column A and find the text "a" and compare the columns B & C and then sum up in column D

Here are the examples below. The result of the formula should be 500. I color the rows where the formula should sum up from.


Sheet2:

Sheet3:

I don't have a single formula solution to offer, however, I do have two possible solutions each requiring helper columns. Note that I've assumed that the format for Sheet2 and Sheet3 is consistent.

[Option 1]

1) Group Sheet2 and Sheet3 by first clicking on the tab for Sheet2 and then CONTROL+Clicking on the tab for Sheet3.

2) Enter the following formula in E2, and copy down:

=IF(A2="A",B2&"#"&C2,"")

3) On your summary sheet, let's say Sheet1, let A2 and B2 contain the sheet names Sheet2 and Sheet3.

4) Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(T(OFFSET(INDIRECT("'"&A2&"'!A2:A7"),ROW(INDIRECT("2:7"))-2,,1))="A",(1/MMULT(COUNTIF(INDIRECT("'"&A2:B2&"'!E2:E7"),INDIRECT("'"&A2&"'!E2:E7")),{1;1}))*(N(OFFSET(INDIRECT("'"&A2&"'!D2:D7"),ROW(INDIRECT("2:7"))-2,,1))),""))+SUM(IF(T(OFFSET(INDIRECT("'"&B2&"'!A2:A7"),ROW(INDIRECT("2:7"))-2,,1))="A",(1/MMULT(COUNTIF(INDIRECT("'"&A2:B2&"'!E2:E7"),INDIRECT("'"&B2&"'!E2:E7")),{1;1}))*(N(OFFSET(INDIRECT("'"&B2&"'!D2:D7"),ROW(INDIRECT("2:7"))-2,,1))),""))

[Option 2]

1) On your summary sheet, let's say Sheet1, let A2 and B2 contain the sheet names Sheet2 and Sheet3.

2) On your summary sheet, select A3:B8. With these cells selected/highlighted, enter the following formula and confirm with CONTROL+SHIFT+ENTER...

=IF(T(OFFSET(INDIRECT("'"&A2:B2&"'!A2:A7"),ROW(INDIRECT("2:7"))-2,,1))="A",N(OFFSET(INDIRECT("'"&A2:B2&"'!B2:B7"),ROW(INDIRECT("2:7"))-2,,1))&"#"&N(OFFSET(INDIRECT("'"&A2:B2&"'!C2:C7"),ROW(INDIRECT("2:7"))-2,,1)),"")

3) Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(A3:B8<>"",(1/COUNTIF(A3:B8,A3:B8))*(N(OFFSET(INDIRECT("'"&A2:B2&"'!D2:D7"),ROW(INDIRECT("2:7"))-2,,1)))))

Hope this helps!

Thank You very much Domenic.

I will be taking Option 2. Once again thank you for taking out of your time to help me.