# Adding a clause to SUMPRODUCT

1. ## Adding a clause to SUMPRODUCT

I can't seem to correctly add in an additional clause to my sumproduct formula below. Trying to add Data2!\$E\$3:\$E\$39073="Active" as an additional clause however having issues. Any help is greatly appreciated!

=SUMPRODUCT((Data2!\$D\$3:\$D\$39073=\$A2)*(Data2!\$F\$1:\$BJ\$1=\$A2),Data2!\$F\$3:\$BJ\$39073)

2. ## Re: Adding a clause to SUMPRODUCT

Maybe:

=SUMPRODUCT((Data2!\$D\$3:\$D\$39073=\$A2)*(Data2!\$F\$1:\$BJ\$1=\$A2)*(Data2!\$F\$3:\$BJ\$39073)*(Data2!\$E\$3:\$E\$39073="Active"))

Can't test it, no data.

Regards, TMS

3. ## Re: Adding a clause to SUMPRODUCT

Thanks TMS. Unfortunately this doesn't work as I get a #VALUE! error which is what I was getting before.

5. ## Re: Adding a clause to SUMPRODUCT

Well, generally, in an Array formula (which is what SUMPRODUCT is), the ranges are usually the same size. But, I assumed that what you had worked, so I tagged on the additional criterion. Comes back as 0 in my test but then I don't have any data to test it with.

Regards, TMS

6. ## Re: Adding a clause to SUMPRODUCT

On a blank worksheet Trevor's formula works and gives 0.

Perhaps there's a problem with the data, not the formula?

7. ## Re: Adding a clause to SUMPRODUCT

Hey All - It was a one cell data error. Thank you all for your help!

8. ## Re: Adding a clause to SUMPRODUCT

Yep, this works:

=SUMPRODUCT((Data2!\$D\$3:\$D\$39073="Q3 2009")*(Data2!\$F\$1:\$BJ\$1="Q3 2009")*(Data2!\$F\$3:\$BJ\$39073)*(Data2!\$E\$3:\$E\$39073="Active"))

But it looks like you have no data that meets the criteria. I changed some data to test it and it will pick up the values

Regards, TMS

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1