Sumifs

**lgibson109** · 09-23-2011, 04:47 PM

Earlier NBVC help me solve a Summing problem, Now that I have that solved I was hoping to be able to do one more thing.

If a value isnt in the array below for the VLOOKUP I wanted to have the function sum those items as long as coulmn AE has an "x". Basically it is summing all the values that contain an "x" in column AE and doesn't match any value in the VLOOKUP Array from column AD (what I would call "others").

Formula to cover those that appear in the Array:

=SUMIFS($X$3:$X$205,$AD$3:$AD$205,VLOOKUP(A210,Expected,4,FALSE),$AE$3:$AE$205,"x")

**daddylonglegs** · 09-23-2011, 04:55 PM

Originally Posted by lgibson109

If a value isnt in the array below for the VLOOKUP

Not sure what that means, do you mean if the VLOOKUP returns #N/A? or if it returns a blank (usually returned as zero?)

**lgibson109** · 09-23-2011, 05:03 PM

What I am trying to do is sum numbers in a column (Y) if the array doesn't have the matching initials that are entered in another column (AD) and column (AE) also has an "x" entered. Right now if the initials don't appear in the array it doesn't sum. I want it to sum only for those items where different initials are entered so I can see add how much "others" did.

**daddylonglegs** · 09-23-2011, 05:14 PM

OK, perhaps I'm missing the obvious......let me have another go....

The formula you quoted

=SUMIFS($X$3:$X$205,$AD$3:$AD$205,VLOOKUP(A210,Expected,4,FALSE),$AE$3:$AE$205,"x")

is summing a range based on two criteria - that's fairly normal....

I think you are saying that you want it to work that way in some circumstances but in other circumstances you want it to ignore the column AD part altogether. Is that right? What I'm not sure of is what circumstances those are - do you mean when A210 is blank? You mention "the array" but I don't know which array you mean.

If it's like I think then you can use an IF function, something like

=IF(A210="",SUMIFS($X$3:$X$205,$AE$3:$AE$205,"x"),SUMIFS($X$3:$X$205,$AD$3:$AD$205, VLOOKUP(A210,Expected,4,FALSE),$AE$3:$AE$205,"x"))

although I imagine that's not quite what you want - can you clarify?

**lgibson109** · 09-23-2011, 05:31 PM

Okay I guess I am not good at explaing what I want, you got the first part right I am summing a range based on 2 criteria. One of those criteria is from the VLOOKUP (in my case it looks up an operators initials say "BH" that are in column AD another criteria is it looks to see if there is and "x" in column AE. When both are satisfied it sums the numbers from column X.

The VLOOKUP I have matches the initials of a regular operator like "BH". Sometime a non regular operator would enter their initials in column AD let's say the initials are "MS". Since these initials aren't found in the VLOOKUP it doesn't sum these numbers.

I was hoping I could get a way to sum column X when there is a value entered in coulmn AD but that value doesn't equal anyones initials (the regulars) set up in the VLOOKUP. The criteria for coulmn AE would still need to "x". If you are still confused I can maybe post a file for you to see. I am new to the forum.

**daddylonglegs** · 09-23-2011, 06:07 PM

Originally Posted by lgibson109

....I am new to the forum....

No problem, I think I see what you mean now.

I think you need to use SUMPRODUCT, you can't do that so easily with SUMIFS, try this version:

=SUMPRODUCT($X$3:$X$205,ISNA(MATCH($AD$3:$AD$205,INDEX(Expected,0,1),0))*($AE$3:$AE$205="x"))

That should give you a sum of column X where AE is "x" and the initials in AD don't match any in the first column of Expected (the lookup range for your VLOOKUP)