Count one column on another column

Question

EDITED

I am trying to count all values in column D/F when column B is "Started"/"Configured". This is what my data looks like.

B              D           F
Started        BARB1       BARB1
Started        BARB2
               BARB3       BARB2
Configured     BARB4
Started

I have tried the following formulas; (CountIf, SumProduct) But it just seems to count them in column B?

=COUNTIF($B$2:$B$1800;B2="Started")
=SUMPRODUCT(($B$2:$B$1800="Started")*($D$2:$CD$1800<>""))

barry houdini · Accepted Answer · 2013-03-15T16:31:33.980

2

This version uses SUMPRODUCT so you don't need CTRL+SHIFT+ENTER

=SUMPRODUCT(ISNUMBER(MATCH(B$2:B$1800;{"started";"configured"};0))*(D$2:F$1800<>""))

edited Mar 15 '13 at 16:31

answered Mar 15 '13 at 15:48

barry houdini

Oh interesting ;) Let me check this. – CustomX Mar 18 '13 at 08:21
Works just as good and preferable because I don't need CTRL+SHIFT+ENTER! :D – CustomX Mar 18 '13 at 08:39

K_B · Answer 2 · 2013-03-15T14:11:14.387

1

Forget what i wrote before, this IS the answer:

 {=SUM(IF((A:A="Started")*(D:D<>"");1;0))+SUM(IF((A:A="Configured")*(D:D<>"");1;0))}

(you know ctrl+shift+enter)

edited Mar 15 '13 at 14:11

answered Mar 15 '13 at 13:50

K_B

Damn, I forgot something in my question. That I got working, but with the edit that's the reason why I couldn't get it to work. Or I'm stupid as anything ... – CustomX Mar 15 '13 at 13:59

2 Answers2