If Column Has a Value Sum Another Column If It Matches 1

[Syndrome78]

Hi All,

I have an Excel spreadsheet with columns that have pricing in some cells, but some of the cells may not have prices in them. I would like to sum the numbers in column V1:V28 if it has a price in column K1:K28 and put the formula in cell K30.


Thanks in advance for all who help

 

[SkipVought]

hi,

A well designed, cogent worksheet table, ought to have HEADINGS in row 1.

Please post a relevant example of the data in these two columns and the corresponding result that you are expecting based on the data in your posted example.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Syndrome78]

Sorry for being vague and I don't have URL's to attach files or pics. I have pricing in column K and counts in column V. I'm trying to sum the counts in column V if there is not a blank cell in column K. So for the sum answer I'm looking for is 90.

K V
blank 10
$100.00 30
blank 25
blank 15
blank 35
$200.00 60
blank 50

Thanks again

 

[SkipVought]

[tt]
=SUMIF(K:K,">"&0,V:V)
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Syndrome78]

Thanks Skip,

Once again you've easily solved my problem and I really didn't think the formula would look that simple. On a side note I've never used and &0 in a formula, what does it do.

 

[SkipVought]

The SUMIF() function has 3 arguments:

1. The RANGE
2. The CRITERIA
3. The SUM RANGE

So argument 2 is the CRITERIA.

I'm using the [highlight #FCE94F]criteria[/highlight]...
[tt]
when the RANGE is [highlight #FCE94F]>0[/highlight] then sum the SUM RANGE
[/tt]

I could have written that as [highlight #FCE94F]">0"[/highlight] or [highlight #FCE94F]">"&0[/highlight] which produce identical results.

The AMPERSAND (&) character in Excel means CONCATENATE TEXT. You can pull up Excel HELP (F1) and look for operators. You will get a list of articles that may be of help understand not only the AMPERSAND but other Excel Operators.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}