CODEWITHSUNDEEP
excelexcel-formula
manoj
manoj

Reputation: 5643

Sum column no in excel with condition

I have a Excel as Below

   A        B     C    D    E
1 Result  sub1  sub2 sub3 Total
2 Ram     sub3  sub2 sub3 2
3 hari    sub1  sub3 sub2 1

here in above example Total = count of correct result ie in B for ram sub3 != sub1 so it is not counted for Total but C and D are right. I am trying it but i can not get result What is formula for total in Excel

Upvotes: 1

Views: 52

Answers (1)

lc.
lc.

Reputation: 116508

You can do this with an array formula:

Use the following formula in E2, then simply fill down. Obviously adjust the ranges if you have more data than your example:

{=SUM(IF($B2:$D2=$B$1:$D$1,1,0))}

Note this is an array formula. To enter it, enter the formula =SUM(IF($B2:$D2=$B$1:$D$1,1,0)), then press Ctrl+Shift+Enter.

This works by doing exactly as you explain. It checks each cell in the range if it equals the value in row 1, then either adds a 1 or a 0 depending on if it matches.

Upvotes: 1

Related Questions