Excel Question

[Bradyams]

Here's an example of what I need:



In the yellow cells I want to sum the values in the "J" column corresponding to the letter in the "I" column. I know that I can use "sumif=I1:I6,"X",J1:J6" for that. Here's the catch though, I only want to use values that have a "No" next to them in the "K" column.

I hope that's clear. Is it possible?

Thanks.

[Dynasty]

An intermediary step should do the job.

Basically, in collumn L, write a forumula which says "If K=Yes, then L=0; If K=No, then L=J.

Then use your original forumula in the new collumn L.

If this hurts the visual presentation, then just hide collumn L.

[Bradyams]

I had an intermediate step in my actual worksheet, but I didn't like the way it looked. I didn't know that you could hide columns though.

Thanks again

[Keats13]

Try (for A, for example)

=sum((i$1:i$6=m2)*(k$1:k$6="No")*j$1:j$6)

Then hit CTRL+SHIFT+ENTER (not just enter)

(The $ are not necessary, but enable you to copy & paste with ease.)

[bravos1]

[ QUOTE ]
Try (for A, for example)

=sum((i$1:i$6=m2)*(k$1:k$6="No")*j$1:j$6)

Then hit CTRL+SHIFT+ENTER (not just enter)

(The $ are not necessary, but enable you to copy & paste with ease.)

[/ QUOTE ]

OR you can just put this formula in N2 and drag it down

=SUMPRODUCT(--($I$1:$I$6=$M2),--($K$1:$K$6="No"),$J$1:$J$6)

If you want to count a yes instead, then just change No to Yes.

These 2 are basically the same except I used SUMPRODUCT where keats just used SUM, but "manually" added the product by multiplying.