Better way to write a sum IF formula?

[leewcrawford]

Hello all,

I basicaly need to look down column Q8 to Q52 for the falue "C19"
If that value is present - Then I need to look up the value of associated
with what is in its respective cell in column O8 to O52.
Then add them all together.

I have the formula that follows and it works, however it is huge. I have to repeat the IF,Vlookup 44 times. Just wondering if there is a better way.

Here is the formula I am using.

=SUM((IF(Q8="C19",(VLOOKUP(O8,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q9="C19",(VLOOKUP(O9,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q10="C19",(VLOOKUP(O10,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q11="C19",(VLOOKUP(O11,clusterequipmentvalues,2,FALSE)),0)),
(IF(Q12="C19",(VLOOKUP(O12,clusterequipmentvalues,2,FALSE)),0)),
.... all the way to C52.


Thanks for the help.

-Lee

[sweep]

Hi,

Try this?

=SUMIF(Q8:Q52,"C19",O8:O52)

[rwgrietveld]

or

=SUMPRODUCT(--IF(Q8:Q52=C19,O8:O52))

[leewcrawford]

Thanks for the quick replys. However this will not work as the column 08 to 052 does not include the values that need to be sumed but a value that is looked up from the table cluster equipment values.

[leewcrawford]

I have enclued a attachment with a sample of what I am looking for.

[JBeaucaire]

Maybe don't understand your example, but the formulas that appear to give you want you want are simple COUNTIF functions.

i14 =COUNTIF(C6:C8,"C19")
i15 =COUNTIF(C6:C8,"C13")

[leewcrawford]

countIF would work if all the each piece of equipment had the same number or connections.

If you look at the example you can see that.

each equipment has eather a C19 or a C13 connecter type.
each equipment has a varied number of connections.

I use vlookup to determin what type of connection
and vlookup to determin how many of thoes connections there are

I need a formula that will add the number of connections there are based on the type of connection.

[JBeaucaire]

So you really want those numbers in D:D, not the count of how many C19s there are? This does that:

i14 =SUMIF(C6:D8,"C19",D6:D8)
i15 =SUMIF(C6:D8,"C13",D6:D8)

[JBeaucaire]

Also, on the final implementation, I would name the data ranges C6:D8 to INLETS and D6:D8 to CONNECTS and then the formula is even easier to read in a HUGE sheet:

=SUMIF(INLETS,"C19",CONNECTS)

[Ron Coderre]

Using your posted workbook....and these Range Names:

RangeName...............Refers To
ClustEquip..............=Sheet1!$B$6:$B$8
CONNECTS................=Sheet1!$D$6:$D$8
INLETS..................=Sheet1!$C$6:$C$8

Then....

I14: =SUMPRODUCT(COUNTIF(I6:I10,ClustEquip)*(INLETS="C19")*CONNECTS)
and
I15: =SUMPRODUCT(COUNTIF(I6:I10,ClustEquip)*(INLETS="C13")*CONNECTS)

Those formula return: 20 and 4, respectively.

Is that something you can work with?

[leewcrawford]

That is correct I need the number is D:D however I need to look up thoes numbers and sum them baised on what is in column I:I using the named array clusterequipmentvalues

I:I 's values are inputed with a list (Data valadation)

They can have more than one of each type of equipment.

So I need to look up its inlet type and how many for each entry. Add up the total for each type for all the equipment listed.

[JBeaucaire]

Well, I'm sure it's clear to you what the list of words in I6:I10 is doing, but it's not to me. Sorry. To me, it just looks like a list of unrelated words.

I see your formula sample below is coming up with the number 20. How is that correct? What do all of these things mean in the real world that make 20 the right answer?

[JBeaucaire]

Ron, I see how he gets 20 now. Duh! Thanks for that!

[leewcrawford]

Perfection Ron! That works great.

Thanks so much everyone for the help.

-Lee