Sum of values in various cells if criteria me

Microsoft Office 2007 home and student
July 27, 2010 at 10:58:54
Specs: Windows Vista
I have multiple sheets (12 months) in my workbook.

I am on the sheet "February Earning 2010" (third page in my workbook) and I am in cell P8.
I need a total amount to show in this cell for all of the previous earnings on the prior two sheets. So I need it to refer to cell C8 because that is where the contract number is located. Then I need it to look for the contract number on:
"December Earnings 2009" and "January Earnings 2010". On each sheet, the contract numbers are listed in column C. The values to be added up is where are get really confused.

The values could be in Q8, S8, U8, V8 or W8 of the row.

Each time the contract number (in row C of each page) is found, I want to add the amounts in the cells (Q8, S8, U8, V8 or W8).

What formula should I use for this?

See More: Sum of values in various cells if criteria me

Report •

July 27, 2010 at 11:15:32

Two questions:
1. Do you only add data from one row on the preceding worksheets - i.e. is there only one row with the required contract number (and it's amounts) or do you need to add up the results from several rows, each containing the required contract number.

2. If you have to get data from columns Q, S, U, V or W, if the value required was in column S (say S8) are Q8, U8, V8 and W8 empty.
If not how can you decide which column to use.


Report •

July 27, 2010 at 11:41:01
1. The contract number will only be shown once on each sheet. They will not be in numerical order.

2. Yes, if the value is in S8 then it would not be in Q8, U8, V8 or W8. It will only ever be in one of those.

Report •

July 27, 2010 at 14:22:43

The easiest way would be to add a column X which sums the values in columns Q to W
So X2 contains:=SUM(Q2:W2)
Then in February's sheet use:
=VLOOKUP(C8,'December Earnings 2009'!$C$2:$X$15,22,FALSE)+ VLOOKUP(C8,'January Earnings 2010'!$C$2:$X$15,22,FALSE)

If you can't add a totals column, then this longer formula should work:
=SUMPRODUCT(('December Earnings 2009'!$C$2:$C$15=C8)*('December Earnings 2009'!$Q$2:$W$15))+ SUMPRODUCT(('January Earnings 2010'!$C$2:$C$15=C8)*('January Earnings 2010'!$Q$2:$W$15))


Report •

Related Solutions

August 9, 2010 at 13:40:42

Sorry for the delay, I have been away on holidays and unable to reach a computer.

If you are still able to continue working on this formula, I would greatly appreciate the help.

I opted to not try the first formula because the values I am looking for will be in column Q, S, U, V or W. Not in R or T.

I used your second suggestion with the changes to the ranges. I am getting a #REF! and the same message in cells afterwards that had a working formula prior.

This is what I put in the cell:

=SUMPRODUCT(('December Earnings 2009'!$C$7:$C$19=C8)*(('December Earnings 2009'!$Q$7:$Q$19)+('December Earnings 2009'!$S$7:$S$19)+('December Earnings 2009'!$U$7:$W$19))+SUMPRODUCT(('January Earnings 2010'!$C$7:$C$34=C8)*('January Earnings 2010'!$Q$7:$Q$34)+('January Earnings 2010'!$S$7:$S$34)+('January Earnings 2010'!$U$7:$W$34)))

You will see the number of entries on each sheet vary. The first entry of each sheet starts on row 7, but depending on the amount of entries the last row varies.

Report •

August 10, 2010 at 07:16:59

1. Because you need to exclude columns R & T in the range Q to W, SUMPRODUCT will have to be restructured like this (for one month):
=SUMPRODUCT(('November Earnings 2009'!$C$7:$C$50=C8)*('November Earnings 2009'!$P$7:$P$50+'November Earnings 2009'!$Q$7:$W$50+'November Earnings 2009'!$Q$7:$W$50))

However SUMPRODCT does not return the expected results - placing the same value in different columns on the November worksheet results in different values. I suspect it depends on how the code for SUMPRODUCT calculates the ranges it uses. When ranges are not consistent - e.g. columns P:Q, column S, columns U:W you get unexpected outcomes.

2. An alternative is to specify each column individually like this (for one month):
=SUMPRODUCT(('December Earnings 2009'!$C$7:$C$50=C8)*('December Earnings 2009'!$P$7:$P$50+'December Earnings 2009'!$Q$7:$Q$50+'December Earnings 2009'!$S$7:$S$50+'December Earnings 2009'!$U$7:$U$50+'December Earnings 2009'!$V$7:$V$50+'December Earnings 2009'!$W$7:$W$50))
By month 12 this is going to be extremely long.

3. As a result I suggest that you use the suggestion that has the sum in column X
Just because you are excluding columns R & T doesn't mean that this will not work:
Use this in column X:

The formula for three months is this:
=SUMPRODUCT(('November Earnings 2009'!$C$7:$C$50=C8)*('November Earnings 2009'!$X$7:$X$50))+SUMPRODUCT(('December Earnings 2009'!$C$7:$C$50=C8)*('December Earnings 2009'!$X$7:$X$50))+SUMPRODUCT(('January Earnings 2010'!$C$7:$C$50=C8)*('January Earnings 2010'!$X$7:$X$50))

By month 12 even this is going to be long!

I also note that you have used different numbers of rows to reflect the differing amounts of data on each month's worksheet.

This is not necessary - use the same range and make it large enough to cover the maximum number of rows likely to be used in any month (and then add a few more rows just in case). SUMPRODUCT will always return zero on the unused rows, because the test =C8 will not be true and will evaluate to zero.

Using one range in all parts of the formula will make trouble-shooting a lot easier.
If you end up with 11 months all with different ranges - you will have 22 ranges in the final formula that need to be checked, if results are not 'as expected' and each pair of ranges will have to match the data on the respective month's worksheet.

I did try your formula on my sample data and it did 'work', meaning that it did not return an error message. It did however give the wrong result and as pointed out in (1) above, the results were also inconsistent, depending on which column a number was placed in: P, Q, S, U, V, or W.

You need to check that none of the cells in any of the referenced month worksheets in columns C, P, Q, S, U, V, or W contains an error value.

Any formula that tries to evaluate another cell containing an error value will itself return an error value


Report •

August 10, 2010 at 07:28:52
When I try the formula in column X as:


(I do not want to add column P as this is where I will have the other formula)

I get the error message #VALUE! I think it may be because there are formulas in each cell but some are blank because there were no results to return. Is there something I should add/change to this simplified formula for columnn X?

Report •

August 10, 2010 at 07:42:00

if =Q8+S8+U8+V8+W8 returns #VALUE, and none of those cells themselves show an error value, then one of the cells is not returning a number or an empty cell "".

You probably need to review the formulas in those cells as they likely should return either a numeric value or a blank cell "" (or possibly zero)

A workaround is to use SUM() as this ignores non numeric values:


Report •

August 10, 2010 at 07:45:48
re: "there are formulas in each cell but some are blank"

I'm jumping in late here, but I'll toss this out anyway:

There are different types of "blank cells".

Both of these can return a blank cell, but the first can cause a #VALUE error if referenced in another formula:

(1) =IF(B1=63, "", B1)

(2) =IF(B1=63, 0, B1)

If you have your options set to not show zero values, both formulas will return "blank" when B1 = 63.

The difference is that you can perform a mathematical operation on a 0 but not on a "".

Let's say B1 = 63 and you have formula (1) in E1.

=E1*2 will return a #VALUE error since Excel can't multiply "" by 2.

However, if you use formula (2) you'll get 0 since 0*2 is zero.

Report •

August 10, 2010 at 13:44:33
Hi Humar,

OK. The "workaround" works great. It has ensured the formula will work.

Thank you also for your most recent post, it has helped me to better understand why I was even getting the error message in the first place. Now, if I need to make changes, I know I must put 0 in stead of " " if I want it to work in a formula.

Thank you so much for your help again. You have very clearly explained what I did wrong, why, and what I needed to do instead.

It been years since I've done this much with excel. College is further in my past each day - I'm thinking another course may be in order.

In the mean time - you've helped me tremendously. Thank you so very much!

Report •

August 10, 2010 at 15:13:47
re: I know I must put 0 instead of " "

I'm not sure if you realize what you typed in that sentence, but there is a difference between " " and "".

" " puts a space in the cell and "" puts nothing.

Try this test:

In A1, enter a 1

Paste this in A2: =IF(A1=1,""," ")

Paste this in A3: =LEN(A2)

A2 will be "blank", A3 should show 0.

Now change A1 to something other than 1.

A2 will still appear to be blank, but the result of the LEN function will now be 1.

Report •

August 11, 2010 at 03:50:32

Thanks for your kind comments.

As to 0 instead of " "
As DerbyDad03 has pointed out " " and "" are two very different beasts.

Also you may need to decide whether you want to return "" (a blank cell) or a zero - there is no right or wrong answer - it depends on what you will be doing with the answer.

Your issue made me look again at "" vs 0 in formulas and it appears that most functions are tolerant of "".
SUM ignores cells containing "", as we saw in the workaround, and functions such as AVERAGE and MAX are also tolerant, but the mathematical operators +, -, *, and / not tolerant and all return the #VALUE error when a cell containing "" is referenced.


Report •

Ask Question