Excel 2007 : Excel 2007 Countsifs challenge

**Paarlite2** · 05-13-2008, 02:10 PM

I did not realize that countifs only worked in Excel 2007, thanks ratcat.

Attached is a zipped Excel 2007 worksheet.

I need to use the Countifs function to populate B39:B43 with the number
of times a birthday "day of the week" (column E) falls on on the same day of the week in column F.

Can anyone help?

Many thanks.

**NBVC** · 05-13-2008, 02:35 PM

I don't have 2007, but if I understand you correctly it would be something like:

=COUNTIFS($E$4:$E$33,C39,$F$4:$F$33,C39)

or

=SUMPRODUCT(($E$4:$E$33=B39)*($F$4:$F$33=B39)) which works in all versions.

**Andy Pope** · 05-13-2008, 02:41 PM

NBVC beat me to the punch on a non version specific formula,

=SUMPRODUCT(($E$4:$E$33=B39)*($F$4:$F$33=B39))

Note that you will need to remove the trailing space from the formula in E4:E33

was =TEXT(D9,"dddd ")
should be =TEXT(D9,"dddd")

**Paarlite2** · 05-13-2008, 02:50 PM

I plugged it in after fixing the error Andy so kindly pointed out but the
formula does not work.

I must use a countifs formula.

I have tried everyone I know and no one seems to know how to
make this work.

Any help greatly appreciated.

**NBVC** · 05-13-2008, 02:52 PM

Not sure if this is the reason why.. but the C39's in my formula should have be B39's

e.g

=COUNTIFS($E$4:$E$33,B39,$F$4:$F$33,B39)

**Paarlite2** · 05-13-2008, 02:53 PM

I tried to plug that in but again all it returned were zeros and I know
that is incorrect.

However, thanks for the suggestion....

**NBVC** · 05-13-2008, 02:54 PM

Originally Posted by Paarlite2

I tried to plug that in but again all it returned were zeros and I know
that is incorrect.

However, thanks for the suggestion....

When I use the Sumproduct Formula after Andy's fix... I get a 1 beside Wednesday and a 1 beside Friday...

Not sure if you noticed my previous post where I amended the Countifs() formula?

**Andy Pope** · 05-13-2008, 02:57 PM

See attached.

2 records match your description. Wednesday and Friday.

**Paarlite2** · 05-13-2008, 03:14 PM

But at least I am not told that it is a circular problem.....

**NBVC** · 05-13-2008, 03:24 PM

Well, like I said I don't have access to Excel 2007, so I can't test the Countifs() but if the Sumproduct() is not working then you have another problem...

Are you sure you amended the formula for procuring the day as Andy mentioned and copied it down the column?

**royUK** · 05-13-2008, 03:30 PM

Check Andy's upload, it demonstrates how to use COUNTIFS to get the same result as the SUMPRODUCT

**NBVC** · 05-13-2008, 03:33 PM

So my Countifs() formula was right then..

**Paarlite2** · 05-13-2008, 03:59 PM

You are both SUPERIOR BEINGS.....

Thank you. I have no idea how it works but it does.

Thank you, thank you, thank you.

**NBVC** · 05-13-2008, 04:05 PM

You are welcome.

Here's a link that explains the Countifs() function:

http://office.microsoft.com/en-us/ex...474941033.aspx

Here's one that explains the Sumproduct() function..and it's great flexibility:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html