COUNTIF with multiple text criteria

**Ted210** · 03-13-2013, 10:41 AM

Hi..... hope someone can help!

I have now tried a few things for counting entries with different text criteria, unfortunately none do far worked.

It normally works that when I use Countif to count all the cells in a certain range that with the word e.g. apple, that I know how many cells have apples.

Now I would like to add that it should only count the word apple in a cell if in a different column the date is Dec 1st.

So.....

01/12/2012 pear
02/12/2012 apple
01/12/2012 apple
03/12/2012 banana
01/12/2012 apple
05/12/2012 apple

for all apples on Dec 1st the result should be 2.

As I already read that Countif doesnt work for multiple criteria, I tried

=SUMPRODUCT((A1:A6="01/12/2012")*(B1:B6="apple"))

I also tried adding the criteria and therefore saying that it should count that what is in a specific cell

=SUMPRODUCT((A1:A6=C1)*(B1:B6=D1))

All result in Error or #N/A

Can someone perhaps help?

Thanks a lot!!

**Tony Valko** · 03-13-2013, 10:45 AM

I also tried adding the criteria and therefore saying that it should count that what is in a specific cell

=SUMPRODUCT((A1:A6=C1)*(B1:B6=D1))

That should work.

Can you post a SMALL sample file that shows the error when using that formula?

**benishiryo** · 03-13-2013, 10:46 AM

hi Ted210, welcome to the forum. could you select the range & press CTRL + SHIFT + ~
you should see all of them become numbers if they are real dates. then undo it. if they are all numbers, try:
=SUMPRODUCT((A1:A6=--("1dec2012"))*(B1:B6="apple"))

otherwise, attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Ted210** · 03-13-2013, 11:30 AM

Thank you for your help.

They are numbers, unfortuntely still doesnt work, so I am attaching the file.

**Tony Valko** · 03-13-2013, 01:33 PM

Ok, there are several problems with this.

The dates in column C also contain the time so when you do a test for the date you have to take the time value into account.

Also in column C, there is a single TEXT entry in cell C99. Is that really supposed to be there?

The size of the ranges in the formula are different. All ranges must be the exact same size.

**Pete_UK** · 03-13-2013, 01:36 PM

Try this in J2:

=SUMPRODUCT((TEXT($C$16:$C$210,"dd/mm/yyyy")="21/02/2013")*($N$16:$N$210=J1))

Note that the ranges have to be the same size. Also, your "dates" contain times, hence the need to use the TEXT function to get them into the same format.

Hope this helps.

Pete

**dilipandey** · 03-13-2013, 01:40 PM

HI Ted,

Use below formula:-

Formula:

Please Login or Register  to view this content.

see attached:-
Survey_2.xls

Regards,
DILIPandey
<click on below * if this helps>

COUNTIF with multiple text criteria

LinkBack

Thread Tools

Rate This Thread

Display

COUNTIF with multiple text criteria

Re: COUNTIF with multiple text criteria

Re: COUNTIF with multiple text criteria

Re: COUNTIF with multiple text criteria

Re: COUNTIF with multiple text criteria

Re: COUNTIF with multiple text criteria

Re: COUNTIF with multiple text criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions