Sumifs/Countifs with multiple and's and or's

**falcontrainer** · 03-19-2013, 08:48 AM

Hello all,

I need to basically rule out using filters on my sheets so I can have multiple calculations run at the same time when new numbers are fed into the sheet. What I am looking to do is is setup like this:

IF C2:C300 = %Application Name%
and
IF A2:A300 = PC* or WC*
and
IF K2:K300 = *XP* or *7*
and
IF L2:L300 '<>test'

I thought I had it before but it keeps adding the numbers incorrectly. Any assistance would be awesome.

Thanks,

**Pauleyb** · 03-19-2013, 09:44 AM

Not sure what you are trying to do. Seems like you want to add something if those conditions are true; maybe a SUMIFS is appropriate? Or, just an IF statement, and then you know what to put in the if_true and if_false sections? Are you assuming the % and * are wildcards or exact matches? I assume * is a wildcard for multiple letters.

**falcontrainer** · 03-19-2013, 10:03 AM

Originally Posted by Pauleyb

Not sure what you are trying to do. Seems like you want to add something if those conditions are true; maybe a SUMIFS is appropriate? Or, just an IF statement, and then you know what to put in the if_true and if_false sections? Are you assuming the % and * are wildcards or exact matches? I assume * is a wildcard for multiple letters.

Sorry for not being more clear but I am trying to count the instances of "ApplicationName" if the other underlying conditions are true. I just had the % to show an application name, nothing significant or relevant. Where the * is a multiple wild card for the other conditions that must be true to count.

**Pauleyb** · 03-19-2013, 10:24 AM

Okay, getting the ORs in a sumifs is not really supported (or at least I don't see an obvious solution). This brings us to SUMPRODUCT
=SUMPRODUCT(--(C2:C300="AppName"),(LEFT(A2:A300,2)="PC")+(LEFT(A2:A300,2)="WC"),NOT(ISERR((FIND("XP",K2:K300))))+NOT(ISERR((FIND("7",K2:K300)))),--(L2:L300<>"test"))
I was also tempted to use DCOUNT, but you said you did not want to use filtering. Not sure if that fits in your definition of DCOUNT or not.

**falcontrainer** · 03-19-2013, 11:49 AM

Originally Posted by Pauleyb

Okay, getting the ORs in a sumifs is not really supported (or at least I don't see an obvious solution). This brings us to SUMPRODUCT
=SUMPRODUCT(--(C2:C300="AppName"),(LEFT(A2:A300,2)="PC")+(LEFT(A2:A300,2)="WC"),NOT(ISERR((FIND("XP",K2:K300))))+NOT(ISERR((FIND("7",K2:K300)))),--(L2:L300<>"test"))
I was also tempted to use DCOUNT, but you said you did not want to use filtering. Not sure if that fits in your definition of DCOUNT or not.

I am getting #N/A when I use the below formula. I tried to adapt it to what I need but found there was a wrong column. I am trying to reference a separate sheet on the same workbook but am getting nothing. This is the 'true' formula that I am running:

=SUMPRODUCT(--(Summary!C2:C30000="Imprivata"),(LEFT(Summary!A2:A30000,2)="PC")+(LEFT(Summary!A2:A30000,2)="WC"),NOT(ISERR((FIND("XP",Summary!I2:I30000))))+NOT(ISERR((FIND("7",SummaryI2:I30000)))),--(Summary!L2:L30000<>"test"))

I am not against using the filters I just don't want to use manual filter on the sheet since it's so huge it takes approx 1 minute to calculate.

**falcontrainer** · 03-19-2013, 11:52 AM

Originally Posted by Pauleyb

Okay, getting the ORs in a sumifs is not really supported (or at least I don't see an obvious solution). This brings us to SUMPRODUCT
=SUMPRODUCT(--(C2:C300="AppName"),(LEFT(A2:A300,2)="PC")+(LEFT(A2:A300,2)="WC"),NOT(ISERR((FIND("XP",K2:K300))))+NOT(ISERR((FIND("7",K2:K300)))),--(L2:L300<>"test"))
I was also tempted to use DCOUNT, but you said you did not want to use filtering. Not sure if that fits in your definition of DCOUNT or not.

I am getting #N/A when I use the below formula. I tried to adapt it to what I need but found there was a wrong column. I am trying to reference a separate sheet on the same workbook but am getting nothing. This is the 'true' formula that I am running:

=SUMPRODUCT(--(Summary!C2:C30000="Imprivata*"),(LEFT(Summary!A2:A30000,2)="PC")+(LEFT(Summary!A2:A30000,2)="WC"),NOT(ISERR((FIND("XP",Summary!I2:I30000))))+NOT(ISERR((FIND("7",SummaryI2:I30000)))),--(Summary!L2:L30000<>"test"))

I am not against using the filters I just don't want to use manual filter on the sheet since it's so huge it takes approx 1 minute to calculate.

It should return with 10,446 if ran correctly.

**Pauleyb** · 03-19-2013, 01:26 PM

30000 lines will take some time using SUMPRODUCT. Not sure if that is the best method at this point, but let's continue down this path, for now.

First, I created a test sheet, and the formula you have should work. I did remove the reference to the Summary sheet, but that should not cause a problem (unless you don't have a sheet named Summary). Does your source data have #N/As in it? Look carefully, as I think this may be the issue since the formula seems sound.

Second, it looks like you are trying to use a wildcard for Imprivata. The check with an '=' will look for an exact match (including the *). If you want it to be anything that starts with Imprivata, then you need to use something similar to the LEFT statements that were used to find "PC" and "WC".

Finally, it's time to use 'Evaluate Formula' to try to find the source of the #N/A. To do that, first modify your equation to reduce the size of the ranges. Essentially, replace every '30000' with '3'. Then go to the Formulas ribbon and hit the 'Evaluate Formula' button. It will allow you to step through the formula and determine where the N/A is originating (if not from the source data).