Hi all, I am trying to use the SUMIF function but for some reason I do not get a result. If I copy an example into my worksheet the example works.



....A..........B......C......D.....E......F.....G. .....H......I.......J......K......L.......M....... N......O
1..Breed.....1.....1a.....2.....2a.....3.....3a... ..4.....4a.....5.....5a.....10.....10a.....11....1 1a
2..1austs.................................1
3..1cav.............1.............1
4..1cav.....................1
5..1cav........................................... ........1
6.. 1tib............................................1

Above is a sample of my spreadsheet. (Had to put ...'s to make columns)
My formula reads =SUMIF(A2:A6,"1cav",B2:O6)
Result should be 4
Result is 0 WHY?????

At first glance this should work, but I think that SUMIF only totals one column, so you are getting the total of column B (which is zero!). I tried it on my Excel 2003 and that is what I get.

Therefore one solution would be to add column P which is the sum of all the preceding columns (ie SUM(B2:O2) and so on). Then your SUMIF would become =SUMIF(A2:A6,"1cav",P2:P6).

One thing to watch is if your column A entry contains "1cav " (ie the correct data but with a space before or after) then your SUMIF formula will not recognise it correctly. Likewise those 1 markers, if they are text and not numeric then the SUMIF won't count them.

HTH
Caspar

Hi

The SUMPRODUCT formula to do this would be:

=SUMPRODUCT(($A$2:$A$6="1cav")*($B$2:$O$6))

With SUMPRODUCT you can add many columns, use many columns as criteria you can work with rows, you can count, sum, calculate average.

SUMPRODUCT is the most powerful function in Excel but more than 90% of users ignore this.

Even the Microsoft's developers ignore the potential of SUMPRODUCT. That is why they have spent Million$ to develop 4 new functions in 2007 that are totally useless (SUMIFS, COUNTIFS, AVERAGEIFS and AVERAGEIF). These new functions are useless because you can do what these functions do with a single function that has been in Excel since 1997 (SUMPRODUCT) and you can even do more with SUMPRODUCT.

Visit Microsoft excel functions and formulas

Thanks CasparV - Using a separate totalled column works perfectly. The fact that SUMIF can only SUM one column is not mentioned anywhere. Thanks heaps.

Hi Pierrevbaexcel - You are a marvel!!! SUMPRODUCT saves me putting in an extra column. I must look in to SUMPRODUCT and see what else I can use it for. Thanks heaps.

Kim

SUMPRODUCT is the single function that I use the most to develop solutions for my clients (large and small enterprises).

Without any macros or any programming I have developed fully automated reporting applications downstream from large central database, accounting programs, sales programs, manufacturing programs....

I have developed permanent inventories and purchasing applications, production planning applications and the most simple and customizable accounting application that allows the user to develop reports by employee, supervisor, sub-project, types of work and any combination thereof.

I have designed a special tutorial on SUMPRODUCT and you can download it from my website along with the applications mentionned above (20 in all).

Visit http:\\Microsoft excel functions and formulas

Macros and VBA are also great tools. Visit

http:\\Free Help on VBA for Excel (Macros)