Hi,
Maybe
=SUMPRODUCT($A$1:$C$4*($D$1:$D$4="A"))
HTH
M.
I'm trying to sumif for a range. Pretend below is an exceil file with A1=1, and D4=A. I wrote "=SUMIF(D1:D4,"A",A1:C4)". The answer would be "7", or 1+2+2 in the first "A" row plus 1+0+1 in the second "A" row. It worked for a while, but now it won't. The problem is A1:C4, it seems the SUMIF command wants this to be A1:A4. I could make a big sumif command "=SUMIF(D1:D4,"A",A1:A4)+SUMIF(D1:D4,"A",B1:B4)+SUMIF(D1:D4,"A",C1:C4)", but would rather be clever.
1, 2, 2, A
2, 2, 1, B
1, 1, 1, C
1, 0, 1, A
Thanks,
Joe
Hi,
Maybe
=SUMPRODUCT($A$1:$C$4*($D$1:$D$4="A"))
HTH
M.
Assuming too much and qualifying too much are two faces of the same problem.
=SUMPRODUCT($C$3:$F$65,($N$3:$N$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.
I actually have (8) columns to add in this matter. I guess the better question is, how can I be more concise with the following formula:
=SUMIF($N$3:$N$65,K67,$C$3:$C$65)+SUMIF($N$3:$N$65,K67,$D$3:$D$65)
+SUMIF($N$3:$N$65,K67,$E$3:$E$65)+SUMIF($N$3:$N$65,K67,$F$3:$F$65)
+SUMIF($N$3:$N$65,K67,$V$3:$V$65)+SUMIF($N$3:$N$65,K67,$W$3:$W$65)
+SUMIF($N$3:$N$65,K67,$X$3:$X$65)+SUMIF($N$3:$N$65,K67,$Y$3:$Y$65).
Notice my (8) columns of C, D, E, F, then V, W, X, Y. Since the criteria is the same (wherever $N$3:$N$65 equals K67), I should be able to create a more concise formula.
joe
Try this=SUMPRODUCT($C$3:$F$65,($N$3:$N$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.
=SUMPRODUCT($C$3:$F$65*($N$3:$N$65="A"))
M.
Aladin
You are right, as usual. Tks for for the advice.Note to Marcelo. The star syntax to over vector/matrix multiplications will run into problems if the matrix reference houses blanks or other text.
(i had not seen it when i made my last post)
M.
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
both worked,
{=SUM(IF($N$3:$N$65=K67,$C$3:$F$65))+SUM(IF($N$3:$N$65=K67,$V$3:$Y$65))}
AND
{=SUM(IF($N$3:$N$65=K67,$C$3:$F$65),IF(N3:N65=K67,$V$3:$Y$65))
I went with the second one. It will hopefully get me thinking about using array formulas more.
Thanks!
In cell B10 - B12 I am trying to add the data from the table above A1:E10. In other words in cell B10 I am trying to sum the data in cells B2 to E2, with the criteria of "Jan" and "a". In all the videos on YouTube I only saw sumifs being used for columns with different criteria, here we have the same criteria. I know you could us a simple "sum" but it is very manual especially if the data is on a different worksheet and it span 100+ columns. I hope the question makes sense. Thank you.
Like this thread? Share it with others