If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

SUMIF only totals the first column

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.

Forget SUMIF use SUMPRODUCT

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.

Sumproduct

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).