Excel help please lads.

Colbaker · 9 Feb 2014 at 20:38

Hi all,

I am trying to do an equation that counts only the cells with the name "Colin" in column B and a blank cell in column D (only count if both these match), I then want to take that number away from all the cells with just "Colin" in Column B

any idea's?

Ekim · 9 Feb 2014 at 21:06

I think this is what you want. There might be an easier solution but I think this should do the job.

Code:

=SUM(IF(((B:B = "Colin")*(D:D = "")), 1))-SUM(IF(((B:B = "Colin")*(D:D <> "")), 1))

This is an array formula so you'd need to use CTRL+SHIFT+ENTER to get it working properly when you put it in. You'd also be better off defining the size of the columns of B and D (should be equal) as at the moment the above will check the entire column.

Dolph · 9 Feb 2014 at 22:17

If you are using 2007 onwards, use countifs. Does the same thing as above but a lot simpler.

=countifs(range 1, criteria 1, range 2, criteria 2...)

Colbaker · 9 Feb 2014 at 23:25

Ekim said:
I think this is what you want. There might be an easier solution but I think this should do the job.

Code:

=SUM(IF(((B:B = "Colin")*(D:D = "")), 1))-SUM(IF(((B:B = "Colin")*(D:D <> "")), 1))

This is an array formula so you'd need to use CTRL+SHIFT+ENTER to get it working properly when you put it in. You'd also be better off defining the size of the columns of B and D (should be equal) as at the moment the above will check the entire column.

Thanks for this, Thinking about it more i might only need an equation than only counts the instances where Column B has "Colin" and Column D is NOT empty, I think i was trying to over complicate what i wanted

, What would you suggest.

Oh and its Excel 2003 so can't use COUNTIFS

Colbaker · 9 Feb 2014 at 23:42

SORRY DOUBLE POST.

Colbaker · 10 Feb 2014 at 10:20

Tried this but it is not working, Just comes up with #n/a..

=SUMPRODUCT(--(B2:B100="Colin")*(--(D2

100<>"")))

Any idea's what i am doing wrong, It should only be counting all the instances where it says "Colin" in Column B and where column D is not Blank on that same Row.

Ekim · 10 Feb 2014 at 13:45

Colbaker said:
Thanks for this, Thinking about it more i might only need an equation than only counts the instances where Column B has "Colin" and Column D is NOT empty, I think i was trying to over complicate what i wanted , What would you suggest.

Oh and its Excel 2003 so can't use COUNTIFS

Code:

=SUM(IF(((B:B = "Colin")*(D:D <> "")), 1))

Should do the job. Its an array formula again so will need to use CTRL+SHIFT+ENTER when you input the formula.