Count specific value if another cell is populated

Hiya,

So I need a formula that will count the amount of a specific word IF that word also has a value in a particular cell

So for example I have a list of locations in column B and I want to search for say Nottingham but I only want it to return a value IF that row that contains Nottingham also has a value in cell G.

B G

1 Nottingham Populated cell
2 Nottingham Blank cell
3 Birmingham Populated cell

So above would count only 1 as only one cell contains Nottingham and a value in G.

I apologise if I haven't worded this right, but I am sure it is an IF/AND formula I just can't work it out. I have got this so far:

=COUNTIF('May 13'!B9:B282,A3)

A3 contains the word Nottingham, so where do I put to only count IF G is also populated?

Thank you in advance.

Hiya both,

Fotis your's worked a treat. Thank you very much, greatly appreciated.

Oeldere your's returned a value of 0 when it should have returned 12, but thank you for your effort it is really appreciated :-)

Hi all,

Got a tricky twist to this one.

One column that I will be referring to for the counting of has numbers in and I need those numbers adding up in the count, so for example:

=SUMPRODUCT(('Jan 13'!$B$7:$B$500=A7)*('Jan 13'!$J$7:$N$500<>""))

Between J7 to N500 the items have any combination of 1 - 5 entered in some of the cells and instead of just counting how many cells have contents I need the values of those contents also adding up.

is this possible?

Hi Fotis,

Absolutely amazing!! Thank you very much :-) I hope to one day know how to do this...it's very impressive.

Thank you again.

Oh getting frustrated now lol.

The formula you said works in my test sheet but on my actual spreadsheet, the page is in another file so I have edited the formula to fit but it returns #VALUE! can you see why at all?

=SUMPRODUCT(('[Medical Loan database 2013.xls]Jan 13'!$B$7:$B$500=A6)*('[Medical Loan database 2013.xls]Jan 13'!$J$7:$N$500<>"")*('[Medical Loan database 2013.xls]Jan 13'!$J$7:$N$500))

Hi,

I tried your formula out on a small sample table of my own and it seems to work fine.

Yeah I think there is a conflict within my existing spreadsheet, as I've done the formula in the actual sheet the data is in and it works fine so it's a little brain boggling but I can work round it.

Thanks for your help though

Hi J.Vangerven I would but it contains confidential information so I could modify it to remove the information but it rather large.

I will use it as is, in fact I worked round it by getting it to work in the main data spreadsheet and just moved the sheet to the statistic spreadsheet and it worked which is even more confusing but I am not complaining as it's working.

Many thanks to all who have helped with this