Assistance with Simplified SumIf Formula

[Sintek]

Hi All

I have a formula to calculate a Total. I have attached my workbook.
Formula is in F8. I need to look up the total sales for all Super 7 products.
Is there a easier way than the following:

=SUM(SUMIF(B8:B844,"*"&"FITCH & LEEDES"&"*",D8:D844)+SUMIF(B8:B844,"*"&"F&L"&"*",D8:D844)+SUMIF(B8:B844,"*"&"MUSGRAVE"&"*",D8:D844)+SUMIF(B8:B844,"*"&"HALLS"&"*",D8:D844)+SUMIF(B8:B844,"*"&"ROSES"&"*",D8:D844)+SUMIF(B8:B844,"*"&"M/FALLS"&"*",D8:D844)+SUMIF(B8:B844,"*"&"NETTARI"&"*",D8:D844)+SUMIF(B8:B844,"*"&"HOPE"&"*",D8:D844)+SUMIF(B8:B844,"*"&"LUCY'S SUMMER GIN"&"*",D8:D844)+SUMIF(B8:B844,"*"&"300ML"&"*",D8:D844)+SUMIF(B8:B844,"*"&"JUICEBOX APPLE FIZZ"&"*",D8:D844))

[PFDave]

Hi

You can use an Array for this, however you will need to amend your list of super 7 products (in Column F) to have * before and after

{=SUM(SUMIF(B:B,$F$11:$F$21,D:D))}

Do not type the curly braces yourself instead input

=SUM(SUMIF(B:B,$F$11:$F$21,D:D))

Using CTRL + SHIFT + ENTER

N.B - your list of super 7 and your text in your formula do not match up so you will get a different result using my method so please amend your list to be accurate

[Sintek]

Hey there PFDave

How would the formula look in VBA code

[Sintek]

Hey there PFDave

How would the formula look in VBA code

[Richard Buttrey]

Hi,

Are the Item codes unique to one of the Super 7 product companies/descriptions in the G11:G21 table.?
e.g. does CFLCS (A45) always belong to F&L or one or other of the super 7?

If so it would be simpler to build a table of Super7 Item codes, then add a helper column to your data and use an

=IF(ISERROR(MATCH(A8,item_code_table,False)),"","Super7") in E8 copied down.

Then use that a simple SUMIF on the helper column that looks for "Super7)

[Sintek]

Hey there guys

Both Solutions will work perfectly. Thanks so much. Not sure which I'll make use of. Am only now starting to build the system.

[PFDave]

Good luck Sintek, and glad I/we could offer some advice.

Thanks also for the rep

[Sintek]

Hey there guys

Both Solutions will work perfectly. Thanks so much. Not sure which I'll make use of. Am only now starting to build the system.

[Sintek]

Thanks Guys

Figured it out.

ws.Range("G9").FormulaArray = "=SUM(SUMIF(B:B,$F$11:$F$21,D:D))"