Hello, I have a list of food items in column A and in column B, I check off when I have bought each item each week by entering the date. Does anyone know of a formula that will report the blanks from column B but against each food type from column A??

I basically want to know how many times I haven't brought a particular food type without having to filter & count them.

Hopefully that has made sense.

Many Thanks

Where are you entering "the check off dates?

Where do you want the result to show up?For example, if you have Beef in A11 and your dates would go in C11:F11, you could use the following formula someplace and it would return Beef X with X being the number of empty cells in C11:F11.

=A11&" " &COUNTIF(C11:F11,"")

Thanks for your reply. I have a list of the food in A so for example, Apple could appear 5 times, Beef could appear 17 times etc. In colum B I just enter the date so for example 10/03/09. On a 2nd tab, I have a list of the 10 food groups I am using, and want a formula next to it to display the number of blanks in colum B from my first tab. Hope this gives more information.

The following array formula seems to work. Assuming your foods are in Sheet1!A1:A18 and your dates/blanks are in Sheet1!B1:B18...

Assuming your food groups start in Sheet2!A1...

Put this in Sheet2!B1 and use Ctrl-Shift-Enter to enter it to put the {} around it. (That makes it an Array Formula)

=SUM((Sheet1!$A$1:$A$18=A1)*(Sheet1!$B$1:$B$18=""))

Then drag it down to the bottom of your list.

It worked!!! Thank you very much for your help :)

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History