SUMPRODUCT, calling a value from a cell as a value to search on
 

I am working on producing stats that reference user ID's and currently
have a SUMPRODUCT expression that reads like this example: -

=SUMPRODUCT(--('Version Control'!$Y$2:$Y$8743="Version Control
Applied"),--('Version Control'!$AC$2:$AC$8743="USERID")) where the
user id is taken from a list.

The User ID would be a value in Column A and the above expression is
in the adjacent cell in Column B

Currently I have to insert the USERID manually.

This is a pain because the list can change daily and then all the
values in each SUMPRODUCT have to be manually amended, which is very
time consuming. I would like the value in A to be addressed dynaically
in the expression in B.

Cheers

Mark

SUMPRODUCT, calling a value from a cell as a value to search on
 

Hello Mark
Simply replace "USERID" with the cell reference in column A
eg for your criteria in cell A1:
=SUMPRODUCT(--('Version Control'!$Y$2:$Y$8743="Version Control
Applied"),--('Version Control'!$AC$2:$AC$8743=A1))

HTH
Cordially
Pascal
a écrit dans le message de news:
...
I am working on producing stats that reference user ID's and currently
have a SUMPRODUCT expression that reads like this example: -

=SUMPRODUCT(--('Version Control'!$Y$2:$Y$8743="Version Control
Applied"),--('Version Control'!$AC$2:$AC$8743="USERID")) where the
user id is taken from a list.

The User ID would be a value in Column A and the above expression is
in the adjacent cell in Column B

Currently I have to insert the USERID manually.

This is a pain because the list can change daily and then all the
values in each SUMPRODUCT have to be manually amended, which is very
time consuming. I would like the value in A to be addressed dynaically
in the expression in B.

Cheers

Mark

SUMPRODUCT, calling a value from a cell as a value to search on
 

On 30 Aug, 10:19, "papou" wrote:
Hello Mark
Simply replace "USERID" with the cell reference in column A
eg for your criteria in cell A1:
=SUMPRODUCT(--('Version Control'!$Y$2:$Y$8743="Version Control
Applied"),--('Version Control'!$AC$2:$AC$8743=A1))

HTH
Cordially
Pascal
a écrit dans le message de news:
m...



I am working on producing stats that reference user ID's and currently
have a SUMPRODUCT expression that reads like this example: -

=SUMPRODUCT(--('Version Control'!$Y$2:$Y$8743="Version Control
Applied"),--('Version Control'!$AC$2:$AC$8743="USERID")) where the
user id is taken from a list.

The User ID would be a value in Column A and the above expression is
in the adjacent cell in Column B

Currently I have to insert the USERID manually.

This is a pain because the list can change daily and then all the
values in each SUMPRODUCT have to be manually amended, which is very
time consuming. I would like the value in A to be addressed dynaically
in the expression in B.

Cheers

Mark- Hide quoted text -

- Show quoted text -

Bonjour Pascal.
I knew it had to be simple but when I tried it first time, before your
posting, I didnt get a match as I had a mismatch on the data.
I cleaned the data up to make sure and it works now, thank you.
If nothing it has taught me to be much more careful with my data!
Merci,
Mark