is there any alternate of sumif(sumproduct?

[Imran Magsi]

Hi Guys!

I am working on lager data and using this formula

Please Login or Register  to view this content.

because of this my file works too slow is there any alternate of above mentioned formula.

[Richard Buttrey]

I'm not sure why you would want both SUMIFS & SUMPRODUCT since the former largely replaced the latter after Excel 2007+

However upload the workbook and manually add some typical results.

[Imran Magsi]

actual i am using name ranged

[Imran Magsi]

a sample file is attached formula strat from Row No. 18

this formula makes my excel files slow and hanging.

Note: in original file i have
100 Items
7 Name Range Categories
12 Months
4 Quarters

[JohnTopley]

Try

=SUMPRODUCT(SUMIFS(D$2:D$17,$B$2:$B$17,$B18,$C$2:$C$17,{"Apple","Orange"}))

OR

=SUMPRODUCT(SUMIFS(E$2:E$17,$B$2:$B$17,$B18,$C$2:$C$17,$O$3:$O$4))

to remove the INDIRECTS

[JohnTopley]

...Or

Code each group of items so

Apples and Oranges are 1, Potato and Tomato are 2 etc then just use standard SUMIFS

=SUMIFS(D$2:D$17,$B$2:$B$17,$B18,$K$2:$K$17,1)

[Imran Magsi]

in sample file i have only 4 items but in original file i have 75 cities and those are grouped east, west, south and north areas. now i want to sum the north area cities in each month.

[JohnTopley]

You only have 7 categories so numbering your 100 products will take 10 minutes of your time.

Changing the formulae provided will take no more.

Both SUMPRODUCT and INDIRECT together are going to impact performance: removing both should see considerable improvement.

Add the cities/regions into SUMIFS (that is what it does best).

[Imran Magsi]

ok thank you sir. i think i should go manual (A2+A11+A20) this will take some time but excel will work fast! May be?

[JohnTopley]

You can use a lookup table (VLOOKUP) to allocate a category to each product so they don't have to be manually entered.

If you posted a file which gives a TRUE representation of your data and reports we may be able to offer other solutions.

[FDibbins]

Try

=SUMPRODUCT(SUMIFS(D$2:D$17,$B$2:$B$17,$B18,$C$2:$C$17,{"Apple","Orange"}))

OR

=SUMPRODUCT(SUMIFS(E$2:E$17,$B$2:$B$17,$B18,$C$2:$C$17,$O$3:$O$4))

to remove the INDIRECTS

This works too..
=SUM(SUMIFS($D$2:$D$17,$C$2:$C$17,{"apple","orange"},$B$2:$B$17,$B18))

[FDibbins]

Deleted my duplicate post - which are really becoming tiresome

[JohnTopley]

@Ford,
Agree re the forum and its performance.

Re the thread question: I am sure adding an extra column (or two) for categories would be the most efficient way.

[FDibbins]

@Ford,
Re the thread question: I am sure adding an extra column (or two) for categories would be the most efficient way.

Agreed. Often, helpers can save a lot of hassles and increase performance

[Imran Magsi]

here is attached file a sample form my original file formula starts from Row No. 1821

NameRange/Group are from Row 2 to Row 70

[JohnTopley]

See attached using VLOOKUP tables: See Sheet1 columns J to O (could be named ranges)

I also suggest you use the format is Sheet2. You can add Quarters AND/OR have the 15/16 data in successive rows

In Sheet2

C3 (jan 2015)

=SUMIFS(Sheet1!$D$2:$D$1820,Sheet1!$B$2:$B$1820,$B3,Sheet1!$G$2:$G$1820,C$1)

C15 (jan 2016)


=SUMIFS(Sheet1!$E$2:$E$1820,Sheet1!$B$2:$B$1820,$B15,Sheet1!$G$2:$G$1820,C$1)

[Imran Magsi]

any update?

[JohnTopley]

See post 16

[JohnTopley]

See attached Sheet3:


In C3

=INDEX(Sheet1!$D$2:$D$1820,MATCH(1,(C$2=Sheet1!$B$2:$B$1820)*(Sheet1!$C$2:$C$1820=$B3),0))

Enter with Ctrl+Shift+Enter

Much easier if data collected in this form.

[Tony Valko]

Deleted my duplicate post - which are really becoming tiresome

Tell the forum owner!

[Imran Magsi]

thank u guys you are really.......... genius