Excel 2007 : Excel 2007 Countsifs challenge

**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