#1
|
|||
|
|||
Excel Question
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. |
#2
|
|||
|
|||
Re: Excel Question
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. |
#3
|
|||
|
|||
Re: Excel Question
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 |
#4
|
|||
|
|||
Re: Excel Question
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.) |
#5
|
|||
|
|||
Re: Excel Question
[ 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. |
|
|