Alternative function to SUM(IF(...))

**G.Bregvadze** · 08-08-2014, 06:06 AM

Hi,

Recently I worked on a project where I have to sum up several values according to Row criteria and Column Criteria from 2d table.

However due to the fact that the data is huge, SUM(if()) function takes too long to calculate the give the result. I tried Sumproduct, but it gives #Value error.

Is there any other substitution to above mentioned formula?

**martindwilson** · 08-08-2014, 06:11 AM

sum product should work but without seeing your data its imposible to guess why it returns #value
also have you tried sumifs()

**G.Bregvadze** · 08-08-2014, 06:16 AM

The reason why Sumproduct returns #value is may be due to the fact that there are blank cells between data, in other works there are several tables from which I want to make summary, so each table is separated from each other, so as I know SUMPRODUCT will return value error when there are such kind of structure.

But how can I use SUMIFS? can you give me some clue? as far as I make summary using Vertical and Horizontal Criteria Ranges.

Thanks in advance for help and support

**martindwilson** · 08-08-2014, 06:24 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**G.Bregvadze** · 08-09-2014, 06:27 AM

Hi sorry for late response.

As requested I uploaded the sample file with sample data and also inserted comment in the cell with detailed description of the issue.

The file: sumproduct_sample.xlsx

**martindwilson** · 08-09-2014, 08:04 AM

the first thing to do is get rid of the errors
=IFERROR(T5-E5,0)

**G.Bregvadze** · 08-09-2014, 08:06 AM

Yes, you are right, thanks for the clue.

But anyway is there any substitution to Sumproduct? can I use SUMIFS in this case for vertical and horizontal criteria ranges?

Alternative function to SUM(IF(...))

LinkBack

Thread Tools

Rate This Thread

Display

Alternative function to SUM(IF(...))

Re: Alternative function to SUM(IF(...))

Re: Alternative function to SUM(IF(...))

Re: Alternative function to SUM(IF(...))

Re: Alternative function to SUM(IF(...))

Re: Alternative function to SUM(IF(...))

Re: Alternative function to SUM(IF(...))

Thread Information

Users Browsing this Thread

Similar Threads

How to use a substitute formula to substitute text entries to a different text entry

substitute for =

[SOLVED] substitute for =

[SOLVED] substitute for =

[SOLVED] substitute

Bookmarks

Bookmarks

Posting Permissions