# SUMPRODUCT Formula, using OR Logic

1. ## SUMPRODUCT Formula, using OR Logic

Good afternoon,

I'm in a bit of a predicament. I only have a basic understanding of the SUMPRODUCT formula to begin with and have been using it within this training tracker for a couple months. It's just come to my attention that on the "Attendance Reports" tab, the "Attended" will only count cells that have a number >0. I would like to figure out how to have it populate based upon the cell containing either 1) a percentage or 2) the letter "A". How would I format that into the formula? Thanks!

Here is the formula, workbook in question is attached with sensitive data redacted.

``Please Login or Register  to view this content.``

2. ## Re: SUMPRODUCT Formula, using OR Logic

See if this helps you out. http://www.utteraccess.com/wiki/Array_Formulas

It's an article on array formulas but it uses SUMPRODUCT as an example of the logic.

When using * instead of -- in SUMPRODUCT, you can think of * as AND and + as OR.

SUMPRODUCT((Range A=B)*((Range C=1) + (Range C="A")) * (Range D))

This gets the sum of D where Range A = B AND (Range C is either 1 OR "A")

3. ## Re: SUMPRODUCT Formula, using OR Logic

Hi, welcome to the forum

My very first comment to you would be to try and avoid using merged cells like you have there. They cause all sorts of problems with formulas, and it is often possible to achieve the same result just by adjusting the row height and column width

2nd, you could probably shorten that formula to this...
=SUMPRODUCT( IF(\$F\$28="All",1,('Training Data'!B\$5:B\$1497=F\$28))*('Training Data'!C\$5:C\$1497>=F\$30)*('Training Data'!C\$5:C\$1497<=F\$32)*('Training Data'!\$D\$4:\$CF\$4=\$F\$34)*('Training Data'!D\$3:CF\$3=F\$36)*(ISTEXT('Training Data'!\$D\$5:\$CF\$1497))*('Training Data'!\$D\$5:\$CF\$1497="N"))

Im not sure which selection to use to test for what you want, but have you tried to just add another condition like you did with "N"?

4. ## Re: SUMPRODUCT Formula, using OR Logic

Originally Posted by FDibbins
Hi, welcome to the forum

My very first comment to you would be to try and avoid using merged cells like you have there. They cause all sorts of problems with formulas, and it is often possible to achieve the same result just by adjusting the row height and column width

2nd, you could probably shorten that formula to this...
=SUMPRODUCT( IF(\$F\$28="All",1,('Training Data'!B\$5:B\$1497=F\$28))*('Training Data'!C\$5:C\$1497>=F\$30)*('Training Data'!C\$5:C\$1497<=F\$32)*('Training Data'!\$D\$4:\$CF\$4=\$F\$34)*('Training Data'!D\$3:CF\$3=F\$36)*(ISTEXT('Training Data'!\$D\$5:\$CF\$1497))*('Training Data'!\$D\$5:\$CF\$1497="N"))

Im not sure which selection to use to test for what you want, but have you tried to just add another condition like you did with "N"?
The "N" on the Attendance Reports is meant to show a No show. The only data that will trigger a count for the No Show is an "N", unlike the Attended which may or may not include a test score. Sometimes we simply need to track attendance without an assessment. Therefore, I'd require the formula to count both a number or the letter "A".

Capture.PNG

The merged cells is something I do actively try to avoid, I'm surprised I didn't actually do as you suggested in the first place. However, I will definitely be fixing that because, as you stated, they are needlessly difficult to work with at times.

5. ## Re: SUMPRODUCT Formula, using OR Logic

Originally Posted by dflak
See if this helps you out.

It's an article on array formulas but it uses SUMPRODUCT as an example of the logic.

When using * instead of -- in SUMPRODUCT, you can think of * as AND and + as OR.

SUMPRODUCT((Range A=B)*((Range C=1) + (Range C="A")) * (Range D))

This gets the sum of D where Range A = B AND (Range C is either 1 OR "A")
That's is what I have gathered from my research, the bit about * being AND and + being OR... Unfortunately, it doesn't seem to want to work the way I think it should when I do it... I attempted to make it work originally using this:

``Please Login or Register  to view this content.``

6. ## Re: SUMPRODUCT Formula, using OR Logic

One more suggestion. Since you are using Excel 2016, you have tables. If you convert the data range to an Excel tables, you get two benefits:
1. if you add more data at the end of the range, you do not have to adjust the formula - Tables "know" how big they are and formulas automatically adjust to reference exactly the right amount of rows.
2. You can use column header names in the formulas. Table1[DOH] easier to remember than what \$C\$5:\$C\$1497 means.

Here is more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

7. ## Re: SUMPRODUCT Formula, using OR Logic

Originally Posted by YUSATrain
....Therefore, I'd require the formula to count both a number or the letter "A"....
Again, not which parts of your data to test this against, but Im thinking that if 1 of your arguments used COUNTA(), that may help?

If you could indicate a (small) section of data that I could use, as well as what you expect, I could do some testing

8. ## Re: SUMPRODUCT Formula, using OR Logic

``Please Login or Register  to view this content.``
I'm used to seeing SUMPRODUCT applied to an nx1 array (a single column or row). I've not seen it applied to multiple rows.

9. ## Re: SUMPRODUCT Formula, using OR Logic

Originally Posted by FDibbins
Again, not which parts of your data to test this against, but Im thinking that if 1 of your arguments used COUNTA(), that may help?

If you could indicate a (small) section of data that I could use, as well as what you expect, I could do some testing
Apologies, attached is a version of the original form except everything is hidden that isn't directly relevant to my goal. The first tab displays a series of data, some "N", "A" and percentages. I am trying to find a way to have both "A" and the percentages count in the "Attended" section of the second tab.

Thank you for your help! I apologize for being difficult.

10. ## Re: SUMPRODUCT Formula, using OR Logic

This appears to give the correct result

=IF(\$F\$28="All",SUMPRODUCT(('Training Data'!\$C\$5:\$C\$568>=F\$30)*('Training Data'!\$C\$5:\$C\$568<=F\$32)*('Training Data'!\$D\$4:\$CF\$4=\$F\$34)*('Training Data'!\$D\$3:\$CF\$3=F\$36)*('Training Data'!\$D\$5:\$CF\$568>0)*('Training Data'!\$D\$5:\$CF\$568<>"N")),SUMPRODUCT(('Training Data'!\$B\$5:\$B\$568=F\$28)*('Training Data'!\$C\$5:\$C\$568>=F\$30)*('Training Data'!\$C\$5:\$C\$568<=F\$32)*('Training Data'!\$D\$4:\$CF\$4=\$F\$34)*('Training Data'!\$D\$3:\$CF\$3=F\$36)*('Training Data'!\$D\$5:\$CF\$568>0)*('Training Data'!\$D\$5:\$CF\$568<>"N")))

11. ## Re: SUMPRODUCT Formula, using OR Logic

Originally Posted by JohnTopley
This appears to give the correct result

=IF(\$F\$28="All",SUMPRODUCT(('Training Data'!\$C\$5:\$C\$568>=F\$30)*('Training Data'!\$C\$5:\$C\$568<=F\$32)*('Training Data'!\$D\$4:\$CF\$4=\$F\$34)*('Training Data'!\$D\$3:\$CF\$3=F\$36)*('Training Data'!\$D\$5:\$CF\$568>0)*('Training Data'!\$D\$5:\$CF\$568<>"N")),SUMPRODUCT(('Training Data'!\$B\$5:\$B\$568=F\$28)*('Training Data'!\$C\$5:\$C\$568>=F\$30)*('Training Data'!\$C\$5:\$C\$568<=F\$32)*('Training Data'!\$D\$4:\$CF\$4=\$F\$34)*('Training Data'!\$D\$3:\$CF\$3=F\$36)*('Training Data'!\$D\$5:\$CF\$568>0)*('Training Data'!\$D\$5:\$CF\$568<>"N")))
Thank you for your reply, Mr. Topley. That formula does in fact work for the given set of data, except it also counts the "P" (Planned) as attended. However, I've gotten around that problem by creating a helper cell and subtracting the "Planned" from the "Attended".

Thank you everyone for your replies! It's very much appreciated. I will go ahead and mark this thread as solved at this point. Even the suggestions given that weren't directly related to my issue here will definitely come in handy as I get more familiar with the advanced formulas in excel.

12. ## Re: SUMPRODUCT Formula, using OR Logic

I am happy you got where you wanted to be, and thanks for the feedback

##### Users Browsing this Thread

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