IF and AND difficulties

[brunette]

Hi there,

After spending the day trying to find answers and trying various different formulas, I think I'm now either gone dumb or really desperate, lol.

Here is what I have:

Column A:
07-Jan

12-Jan
12-Jan

Column B:
12
12
13
14

What I need is to count each type of data in Column B AND count all the blank cells in column A that correspond to that type of data in B.

i.e. Count all the blank cells for 12, 13 etc. If there are no blank cells for a corresponding number, then it will show 0, otherwise it should count all the blank cells (in this case, 1).

I need the forumula to check the entire column, because this will eventually go into a macro as the data will change every month and can be either more or less than in the example.

Any help with the formula, please? I'd be most grateful.

I'm sorry if it sounds confusing or garbled. It's been a long, hard week and I've been hitting my head against the wall with this for most of it and I'm bracing myself to feel like an absolute idiot when the undoubtedly simple answer is revealed *sigh*.

Thanks in advance,

 

[AlphaFrog]

If you have Excel 2003 or earlier, use SUMPRODUCT

Excel Workbook
	A	B	C	D	E
1	7-Jan	12		B-type	Counts
2		12		12	1
3	12-Jan	13		13	0
4	12-Jan	14		14	0
...

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(--($B$1:$B$1000=D2),--($A$1:$A$1000=""))
E3	=SUMPRODUCT(--($B$1:$B$1000=D3),--($A$1:$A$1000=""))
E4	=SUMPRODUCT(--($B$1:$B$1000=D4),--($A$1:$A$1000=""))

If you have Excel 2007 or later, you could use COUNTIFS instead e.g.
=COUNTIFS($B:$B, D2, $A:$A, "")

 

[brunette]

Thanks for your reply!

This could be because I don't really understand the purpose of the function, but copying and pasting it did not work for me. It returned an error, pointing to the comma in the middle of the two parenthesis'd parts of the formula.

I am indeed using Excel 2003.

In order for the formula to work, will I have to create the new columns you did? I'm at home now so I'll give it a bash on Monday when back in the office. Thanks for all your insights on this, I really do appreciate the help and the learning!

Regards,

 

[brunette]

Hi there,

Gave the above suggestion a bash this morning, now that I understand the formula etc, and it works, now that I figured out I've to replace the (,) for a ( ; ).

Thanks again for your help