SUMPRODUCT returns #VALUE for a relatively simple function

[janpasierb]

Hi

I have a problem. I need to use SUMPRODUCT. Let's say I have a cell with the desired formula on "AR2". I need to:

- check whether the value in the current table, in the "Day" column in the same row is equal to the value in another table ("AllBugsTable"), column "Created", same row,

- check whether the value in another table ("AllBugsTable") column "Priority" is equal to word "Blocker",

- check whether the value in another table ("AllBugsTable") column "Affects Version/s" DOES NOT contain the word "backlog" in it, same row,

- check whether the value in another table ("AllBugsTable") column "Affects Version/s" DOES NOT contain the word "archive" in it, same row,

- check whether the value in another table ("AllBugsTable") column "Fix Version/s" DOES NOT contain the word "backlog" in it, same row,

- check whether the value in another table ("AllBugsTable") column "Fix Version/s" DOES NOT contain the word "archive" in it, same row,

If all the above are true then the sumproduct should be calculated.

This is what I have so far:

=SUMPRODUCT(
(ISNUMBER(MATCH([@Day], AllBugsTable[Created], 0))),
(ISNUMBER(MATCH("Blocker", AllBugsTable[Priority], 0))),
(ISNA(MATCH("*backlog*", AllBugsTable[Affects Version/s], 0))),
(ISNA(MATCH("*backlog*", AllBugsTable[Fix Version/s], 0))),
(ISNA(MATCH("*archive*", AllBugsTable[Affects Version/s], 0))),
(ISNA(MATCH("*archive*", AllBugsTable[Fix Version/s], 0))))

but I get #VALUE back. What am I doing wrong?

Thanks
Jan

 

[janpasierb]

EDIT: The other formula I came up with is:

=SUMPRODUCT(
(AllBugsTable[Priority]="Blocker")*
(AllBugsTable[Created]=[@Day])*
(ISNA(SEARCH("backlog", AllBugsTable[Affects Version/s])))*
(ISNA(SEARCH("archive", AllBugsTable[Affects Version/s])))*
(ISNA(SEARCH("backlog", AllBugsTable[Fix Version/s])))*
(ISNA(SEARCH("archive", AllBugsTable[Fix Version/s]))))

 

[janpasierb]

anyone?

 

[Jonmo1]

Try

=SUMPRODUCT(
--(ISNUMBER(MATCH([@Day], AllBugsTable[Created], 0))),
--(ISNUMBER(MATCH("Blocker", AllBugsTable[Priority], 0))),
--(ISNA(MATCH("*backlog*", AllBugsTable[Affects Version/s], 0))),
--(ISNA(MATCH("*backlog*", AllBugsTable[Fix Version/s], 0))),
--(ISNA(MATCH("*archive*", AllBugsTable[Affects Version/s], 0))),
--(ISNA(MATCH("*archive*", AllBugsTable[Fix Version/s], 0))))

 

[janpasierb]

Try

=SUMPRODUCT(
--(ISNUMBER(MATCH([@Day], AllBugsTable[Created], 0))),
--(ISNUMBER(MATCH("Blocker", AllBugsTable[Priority], 0))),
--(ISNA(MATCH("*backlog*", AllBugsTable[Affects Version/s], 0))),
--(ISNA(MATCH("*backlog*", AllBugsTable[Fix Version/s], 0))),
--(ISNA(MATCH("*archive*", AllBugsTable[Affects Version/s], 0))),
--(ISNA(MATCH("*archive*", AllBugsTable[Fix Version/s], 0))))

Thanks for suggestion but it doesn't work. The cells with that formula always spit out 0.

 

[Jonmo1]

Thanks for suggestion but it doesn't work. The cells with that formula always spit out 0.

Well, that would be an improvement then, becuse originally you were getting #VALUE! Errors..

but I get #VALUE back. What am I doing wrong?

If it's returning 0, then that means none of the rows meet ALL of the given criteria.

 

[Jonmo1]

I think probably the SEARCH is better than the MATCH.
Don't think sumproduct will support the use of wildcards in the Match.

But the ISNA part won't work, because SEARCH Returns #VALUE! when the search doesn't find what it's looking for.
So use ISERROR instead.

Try

=SUMPRODUCT(
--(AllBugsTable[Priority]="Blocker"),
--(AllBugsTable[Created]=[@Day]),
--(ISERROR(SEARCH("backlog", AllBugsTable[Affects Version/s]))),
--(ISERROR(SEARCH("archive", AllBugsTable[Affects Version/s]))),
--(ISERROR(SEARCH("backlog", AllBugsTable[Fix Version/s]))),
--(ISERROR(SEARCH("archive", AllBugsTable[Fix Version/s]))))

 

[janpasierb]

What a pro. Thank you, it worked. Where do I accept your post as an answer?

 

[Jonmo1]

Glad to help, thanks for the feedback.

What a pro. Thank you, it worked. Where do I accept your post as an answer?

You just did.

We don't close threads or mark them resolved at MrExcel.Com
Your acknowledgement of resolution is sufficient.