# SUMPRODUCT. using AND-OR logic with text

1. ## SUMPRODUCT. using AND-OR logic with text

Hello,

I am struggling with a SUMPRODUCT formula that essentially "ANDs" a criteria with multiple "OR" criteria to produce a result. The "OR" is more than one text strings that may exists in a range. I have not been able to make the formula work. I hope someone can point out what is wrong and can provide some explanation on how to correct it or if there is a better way to do this.

To better explain the problem and the result I desire, I have attached a sample Excel file. Column A contains text in each row. Corresponding to each text is a date in Column B.

Cell B11 contains the formula. I first compare the date range (B4:B8) with a threshold date in B1. This produces an array {1,0,1,0,0} where Cell B4 and B6 match the threshold date. I then search column A from A4:A8 looking for the text "CAB" OR "VIC". The second array of the SUMPRODUCT should produce a result {1,0,1,1,0} (which it does not). My thought was when the second array is "ANDED" with the first (date) array, the resulting array would be {1,0,1,0,0}. The sum of this would be 2. Instead, I get a value error.

Thanks.

2. ## Re: SUMPRODUCT. using AND-OR logic with text

Try this...

=SUM(COUNTIFS(A4:A8,{"CAB*","VIC*"},B4:B8,B1))

3. ## Re: SUMPRODUCT. using AND-OR logic with text

Try

=SUMPRODUCT((B4:B8=B1)*(LEFT(A4:A8,3)={"CAB","VIC"}))

4. ## Re: SUMPRODUCT. using AND-OR logic with text

Thank you Tony and Jonmo.

The SUMPRODUCT formula worked. I also see the mistake I made in my formula.

The SUM(COUNTIFS(---)) looks like a neat way to accomplish what I was trying. I tried it and it works too. I can't use it because of Excel 2003 that is still in use at my place.

5. ## Re: SUMPRODUCT. using AND-OR logic with text

You're welcome. Thanks for the feedback!

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