#VALUE! error when trying to use 2 criteria in SUM() function 1

[DAmoss]

Hi,

I'm using the formula shown below (Excel 2003) to generate some required total figures, but I keep getting the dreaded #VALUE! error even though I can see the correct value being generated in the function arguments box when it is opened.

I know it's giving this error because there are text & numeric values being returned, but how do I get around this annoying problem?

=SUM((INDEX(sysClaimsAll,,1)="ABG")*(INDEX(sysClaimsAll,,6)="Housing")*INDEX(sysClaimsAll,,VLOOKUP($E$19,sysClaimsOffSet,2)))

I'm trying to sum up all the rows that have both 'ABG' (column 1) & 'Housing' (column 6) alongside each other, is there another way to do the above? I've been looking at various examples on the internet and the above is the nearest I've gotten to a working solution, unfortunately I can't quite get it to work as I want.

To explain some of the above formula:

sysClaimsAll:
This my named array, the first six columns hold various text values, all the columns after these hold my various figures.

INDEX(sysClaimsAll,,VLOOKUP($E$19,sysClaimsOffSet,2))
This part of the formula is generating the correct value required and as such can be ignored, it is using a dropdown list (located at cell $E$19) to lookup a column number in a lookup table, this column number is the one that I require totalling.

Thanks in advance!

 

[kjv1611]

Perhaps it has to be an array formula? Have you tried that?

(In case you're not aware of them)
1. Select the cell, place your cursor at the end of the formula (or I suppose it doesn't have to be at the end of the formula)
2. Hold the <Ctrl> button on your keyboard and press <Enter>

If that fixes it, then that's all you're missing.

For info on array formulas, there are multiple sources, including the helpfiles. However, here's a couple I have often used:

http://www.cpearson.com/excel/ArrayFormulas.aspx

and

John Walkenbach's book, Excel 2003 Formulas and here's a short mention on his site:

http://j-walk.com/ss/excel/usertips/tip048.htm

And well, there are lots of others - Ozgrid, MrExcel, etc... Google is your best friend for more.

 

[SkipVought]

Hi,

Post some sample data and the value you expect from that sample, using your formula.

I got your formula to work as an ARRAY forula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

I'm trying to sum up all the rows that have both 'ABG' (column 1) & 'Housing' (column 6) alongside each other, is there another way to do the above?

Yes!

Most of us around here would use a SumProduct formula to accomplish that. It's quite simple once you understand how to read it and has the benefit of not having to be entered as an Array Formula. Plus it's quite a bit shorter than what you have above.

[tab][COLOR=blue white]=SumProduct( --($A$1:$A$1000 = "ABG") * --($F$1:$F$1000 = "Housing") )[/color]

That will count how many times both columns A and F meet your criteria. To instead sum a different column, you just need to add a third section with a range but no criteria. That's a little trickier in your case since you want the column that's summed to vary. Below I solve that by using your existing lookup table:

[tab][COLOR=blue white]=SumProduct( --($A$1:$A$1000 = "ABG") * --($F$1:$F$1000 = "Housing") * (Indirect(VLookup($E$19, sysClaimsOffSet, 2, 0) )[/color]

You'd just need to change the second column in sysClaimsOffSet to contain range references instead of column numbers.
e.g.[tt]
[tab]$G$1:$G$1000
[tab]$H$1:$H$1000
[tab]$I$1:$I$1000
[tab]$J$1:$J$1000[/tt]

Notes about SumProduct:
* Each section of the formula must contain the same number of rows
* You cannot use an entire column reference with SumProduct (i.e. A:A won't work)
* SumProduct actually functions as an Array Formula behind the scenes, but you don't have to enter with [Ctrl]+[Shift]+[Enter] ( KJV: You forgot the Shift)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[DAmoss]

Thanks guys for your quick responses ... I had tried the SUMPRODUCT() earlier, but I must have had a couple of missing brackets ... hence it didn't work before.

Shown below is the final solution that does work

=SUMPRODUCT((INDEX(sysClaimsAll,,1)="ABG")*(INDEX(sysClaimsAll,,6)="Housing")*(INDEX(sysClaimsAll,,VLOOKUP($E$19,sysClaimsOffSet,2))))

But thanks to 'AnotherHiggins' little example above I could quickly see where I had gone wrong ... it works a treat now, Cheers!

 

[SkipVought]

1) each table should have ONE ROW of unique headings

2) in Excel 2003 and earlier, I always use HEADING NAMED RANGES, via Insert > Name > create -- Create names in TOP row.

3) If wwe were to assume that your 6 columns have headings named HEAD1 thru HEAD6, then you formula may look like...
[tt]
=SUMPRODUCT((HEAD1="ABG")*(HEAD6="Housing")*(INDIRECT(HLOOKUP($E$19,1:1))))
[/tt]
where the HLOOKUP finds a heading name


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]