CODEWITHSUNDEEP
excelexcel-formula
Bryan Davies
Bryan Davies

Reputation: 430

Countif sum formula

Looking to create a formula that looks at the first row of of a table, runs a countif, then multiple that value by constant.

It then looks at the 2nd row, runs the countif, the multiples it by a 2nd value.

I know i can run multiple countif * X + countif * Y etc, but for the data I am using, its a bit too big.

I've tried using an array formula;

={COUNTIF(OFFSET($B$2:$U$10,{0,1,2,3,4,5,6,7,8},0,1,COLUMNS($B$2:$U$10)),A29)*{$V$2,$V$3,$V$4,$V$5,$V$6,$V$7,$V$8,$V$9,$V$10}

So this is trying to countif B2 to U2 is the same as A29 then multiple by V2, Then add B3 to U3 compared to A30 then multiple by V3.

Excel just says there is a problem with the formula and refuses to even try to fix it.

I hope this is clear. I think my problem is using arrays with countif's, but I am unsure

Upvotes: 1

Views: 181

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Building on @R3uK's comment:

=SUMPRODUCT(($B$2:$U$10=$A$29)*$V$2:$V$10)

Column W is just to show it comes with the correct answer.

enter image description here

Also, If there is a chance that column V will contain errors or text by seperating them into their own criteria will skip those that have them:

=SUMPRODUCT(--($B$2:$U$10=$A$29:$A$37);$V$2:$V$10)

The -- changes the Boolean TRUE/FALSE to their bit equivalents 1/0 so it can then be multiplied to the results on column V.

Upvotes: 2

Related Questions