Or Statement has me in circles

[Robizzle512]

Need an "If" statement that has to only have 1 constant variable and 4 others that be a mix. To put it simply looking to count how many of a certain subset of policies were sold be a specific rep. The sumif will only count it they all meet the criteria which they will not have all 4 products but could have sold multiple of 1 or two. The Sales Incentive preview is where we will compile the raw data from All Add Ons sheet. In column "L" of the All Add Ons sheet is the 4 products considered as Home with "Home" being included in the 4.
I tried to break it down to its simplest form and search just for "Home" and the simple sumif works but converting to the "Or" statement and looking for just "Home" gives a 0..... Help?

 

[Fluff]

Hi & welcome to MrExcel.
You can do that like

=sum(countifs(B:B,H1:H4))

Just add the sheet names & the other criteria.

 

[Robizzle512]

Hi & welcome to MrExcel.
You can do that like

=sum(countifs(B:B,H1:H4))

Just add the sheet names & the other criteria.

I knew it should have been much simpler than I was attempting thank you but where could I include the "And" statement so that it is only looking for those products by Advisor? I attempted to add the "And" as Criteria 2 to look for "Service Advisor 1" specifically but it breaks it again.

 

[Robizzle512]

I knew it should have been much simpler than I was attempting thank you but where could I include the "And" statement so that it is only looking for those products by Advisor? I attempted to add the "And" as Criteria 2 to look for "Service Advisor 1" specifically but it breaks it again.

=SUM(COUNTIFS('All Add-ons'!B:B,'All Add-ons'!L1:L4))
This is where I am at now and It is confirmed counting all within that range properly. Just want to narrow down to the count by specific advisor

 

[Fluff]

How about

=SUM(COUNTIFS('All Add-ons'!B:B,'All Add-ons'!L1:L4,'All Add-ons'!D:D,A2))

 

[Robizzle512]

If I could like this more than once I would,, you are much appreciated!!!

 

[Fluff]

You're welcome & thanks for the feedback.