Sum only cells with text in a neighbouring cell

M

matt_the_brum

I have a list of values (values of quotes) in a column. In the nex
four columns I put an 'x' in one of them to note whether the value i
'possible', 'probable', 'lost' or 'order'. How can I sum the value
for each category? I can COUNT them, SUMIF doesn't seem to be capable
do I need to VLOOKUP? Thanks for any hel
 
N

Norman Harker

Hi matt_the_brum!

Beneath column B use:

=SUMIF(B1:B16,"x",$A$1:$A$16)

Copy across to E

You have the sum of values in A where there is an x in the column B
(and C, D, E, when copied to those columns)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
M

matt_the_brum

Excellent Norman, thanks for that. Just had to change the refernces to
a column on another worksheet. Also, when copying the formula across
the target column also changed. Had this problem before and never
worked ouot hoe to fix part of a formula when copying across.
 
M

matt_the_brum

OK, that worked but now I want to add another criteria. I want to ad
the cells with an 'x' in the next colun and with a January dat
(xx/01/04) in a column to the left of it
 
R

Ron Rosenfeld

I have a list of values (values of quotes) in a column. In the next
four columns I put an 'x' in one of them to note whether the value is
'possible', 'probable', 'lost' or 'order'. How can I sum the values
for each category? I can COUNT them, SUMIF doesn't seem to be capable,
do I need to VLOOKUP? Thanks for any help
Click to expand...

SUMIF should work. Perhaps you could post examples of your data (in text), the
formula you are using, and the results.

SUMIF(range,criteria,sum_range)

For example, if your DATA was in A1:A1000, and your 'possible' in B1:B1000, the
formula for the total of 'possible' should be:

=SUMIF(B1:B1000,"x",A1:A1000)


--ron
 
F

Frank Kabel

OK, that worked but now I want to add another criteria. I want to
add
the cells with an 'x' in the next colun and with a January date
(xx/01/04) in a column to the left of it.
Click to expand...

Hi
try something like
=SUMPRODUCT((C1:C16="x")*(B1:B16>=DATE(2004,1,1))*(B1:B16<DATE(2004,2,1
)),$A$1:$A$16)

just change the column indices to your need

Frank
 
M

matt_the_brum

Sorry, bit of a muddled explaination, I'll try to explain it clearly.

All data is on worksheet 'Enquiries'.
Date a quote was asked for is in column D.
The value of our quote is in column G.
Columns H, I, J and K are for me to put an 'x' in to note whether the
quote is a 'possible', 'probable', 'lost', or 'order'.
On another worksheet I can sum the 'possible' jobs we have quoted by

=SUMIF(Enquiries!H:H,"x",Enquiries!G:G)

This works fine for all the jobs we have quoted.

Question
How can I sum all the 'possible' jobs quoted for only January?
So there are 2 criteria, an 'x' in column H and any January date in
column D.
 
N

Norman Harker

Hi matt_the_brum!

I'm afraid that the big defect / limitation of COUNTIF and SUMIF is
that they will only handle one criteria. They don't even allow for an
AND or OR as the criteria.

We need to use other functions for this and perhaps the most commonly
used is SUMPRODUCT.

In this case I have dates recognised as dates by Excel in column A. I
have my amounts in column B. In C, D, E, and F I have my x's (letters
that is, not ex-wives)

Under column C I use:

=SUMPRODUCT((MONTH($A$1:$A$16)=1)*(C1:C16="x")*($B$1:$B$16))

Copy across under D,E and F

You might regard the two criteria structures within the SUMPRODUCT
formula as being implicit IF functions that resolve to 1 if TRUE and 0
if FALSE. Only if both are TRUE will you get 1*1* B_value added to the
sum of the products.

I could do better if I substitute 1 by a cell reference immediately to
the left of my formula (e.g.) use $B18. Then in B18 down to B29 I have
the month numbers. That allows me to copy across and down to get a
table.

Although I might start off with this under the data, after it is all
working sweetly I cut and paste to a more convenient location.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
M

matt_the_brum

Thought it might work but its returning an error.
LOL at ex-wives, had to explain what I was laughing at to colleagues.

I tried

=SUMPRODUCT((MONTH($D$1:$D$1000)=1)*(H1:H1000="x")*($G$1:$G$1000))

At the bottom of column H. Dates are formatted as dates in column D
values in G, x's in H.

Using SUMIF and selecting the values manually isn't too much of
problem, but long term would be easier if I could get this working
 
F

Frank Kabel

Hi
is row 1 eventually a heading row (that is G1 contains text). If yes,
try
=SUMPRODUCT((MONTH($D$1:$D$1000)=1)*(H1:H1000="x"),$G$1:$G$1000)
Frank
 
N

Norman Harker

Hi matt_the_brum

I tested your formula and it works OK! Except that it will add all of
the Januarys together even if in different years. If there is more
than 1 year involved, you would need to extend it using:

=SUMPRODUCT((MONTH($D$1:$D$1000)=1)*(YEAR($D$1:$D$1000)=2004)*(H1:H100
0="x")*($G$1:$G$1000))

Or you could use Frank's approach of >= Jan 1 and <= Jan 31

But trust me! It works.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
R

Ron Rosenfeld

Sorry, bit of a muddled explaination, I'll try to explain it clearly.

All data is on worksheet 'Enquiries'.
Date a quote was asked for is in column D.
The value of our quote is in column G.
Columns H, I, J and K are for me to put an 'x' in to note whether the
quote is a 'possible', 'probable', 'lost', or 'order'.
On another worksheet I can sum the 'possible' jobs we have quoted by

=SUMIF(Enquiries!H:H,"x",Enquiries!G:G)

This works fine for all the jobs we have quoted.

Question
How can I sum all the 'possible' jobs quoted for only January?
So there are 2 criteria, an 'x' in column H and any January date in
column D.
Click to expand...

I think you've already gotten several answers from Norman and Frank. So if
they are working for you, I'll butt out.


--ron
 
Top