• Status: Solved
• Priority: Medium
• Security: Public
• Views: 178

# SUMPRODUCT Update

Hi,
Quick question, still learning how to work with sumproduct.  I have the following formula and I would like to add the criteria AS <>Yes how would I do that?

=SUMPRODUCT((MOD(ROW(\$AO\$10:\$AO\$484)-ROW(AP10),2)=1)*1,\$AO\$10:\$AO\$484)
0
jmac001
• 3
• 2
• 2
• +1
2 Solutions

Programmer AnalystCommented:
http://www.mrexcel.com/forum/excel-questions/460593-nesting-if-statement-sumproduct.html

Let me know if that is going to work for you or not.
0

Commented:
jmac001,

Just add       \$AS\$10:\$AS\$484<>"Yes"       as an argument before the final one, like

=SUMPRODUCT((MOD(ROW(\$AO\$10:\$AO\$484)-ROW(AP10),2)=1)*1,\$AS\$10:\$AS\$484<>"Yes",\$AO\$10:\$AO\$484)

Matt
0

Author Commented:

Matt - Tried your solution and received 0 for my value? The current sum is 225 and with the updated function the new total should be 197.
0

Programmer AnalystCommented:
Can you post what you tried just now?
0

Commented:
Is it possible to post your workbook? I'm a little puzzled by what you're trying to accomplish with your mod statement. If you're only trying to get odd numbers, try:

=SUMPRODUCT((MOD(ROW(\$AO\$10:\$AO\$484),2)=1)*(\$AS\$10:\$AS\$484<>"Yes")*(\$AO\$10:\$AO\$484))

Matt
0

Commented:
I think the original formula sums every other row starting with the second cell in the range

Matt's last version also does that but the reason for the  ROW(Range)-MIN(ROW(Range)) type construction is that the formula will be more robust, in that it will still return the same value, even if you insert or delete rows above the referenced range.

To retain the original robust intent but add that criteria you can use this version:

=SUMPRODUCT((MOD(ROW(\$AO\$10:\$AO\$484)-ROW(\$AO\$10),2)=1)*(\$AS\$10:\$AS\$484<>"Yes"),\$AO\$10:\$AO\$484)

Note I replaced AP10 with \$AO\$10 as that more closely resembles the ideal setup

regards, barry
0

Commented:
in that it will still return the same value, even if you insert or delete rows above the referenced range.
Nice catch. I was wondering about that
0

Author Commented:
Thanks Barry and Matt, the solution worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.