How Can i combine the following into one cell:-

=COUNTIF('Mar 07'!$I$1:$I$527,2001)

=COUNTIF(H:H,"5") - COUNTIF(H:H, "<0")

Any help Please
Monty

Something like this??

=COUNTIF($I$1:$I$527,2001)&" and
"&COUNTIF(H:H,"5")-COUNTIF(H:H,"<0")


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=564611

Dave

Thanks for this See below for what i am trying to acheive.

In column (I) I have cost centres, which range from 2001 to 2042 and in
column (H) I have the number of days taken for payment. In cell AA4 and I
want to return all payments over 5 days belonging to cost centres
2033,2036,2037 & 2041.

Can you please help.

Monty

"davesexcel" wrote:


Something like this??

=COUNTIF($I$1:$I$527,2001)&" and
"&COUNTIF(H:H,"5")-COUNTIF(H:H,"<0")


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=564611

Try:

=sumproduct(--($I$1:$I$527=2001),--($H$1:$H$5275))

Sumproduct must have arrays (i.e. cannot be columns e.g H:H) and they must
be of same size.

HTH

"Monty" wrote:

Dave

Thanks for this See below for what i am trying to acheive.

In column (I) I have cost centres, which range from 2001 to 2042 and in
column (H) I have the number of days taken for payment. In cell AA4 and I
want to return all payments over 5 days belonging to cost centres
2033,2036,2037 & 2041.

Can you please help.

Monty

"davesexcel" wrote:


Something like this??

=COUNTIF($I$1:$I$527,2001)&" and
"&COUNTIF(H:H,"5")-COUNTIF(H:H,"<0")


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=564611

cheers for this, one more thing how can i add more cost centres to the first
line for example:-
=sumproduct(--($I$1:$I$527=2001,2002,2003),--($H$1:$H$5275))

Thanks again

Monty


"Toppers" wrote:

Try:

=sumproduct(--($I$1:$I$527=2001),--($H$1:$H$5275))

Sumproduct must have arrays (i.e. cannot be columns e.g H:H) and they must
be of same size.

HTH

"Monty" wrote:

Dave

Thanks for this See below for what i am trying to acheive.

In column (I) I have cost centres, which range from 2001 to 2042 and in
column (H) I have the number of days taken for payment. In cell AA4 and I
want to return all payments over 5 days belonging to cost centres
2033,2036,2037 & 2041.

Can you please help.

Monty

"davesexcel" wrote:


Something like this??

=COUNTIF($I$1:$I$527,2001)&" and
"&COUNTIF(H:H,"5")-COUNTIF(H:H,"<0")


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=564611

=SUMPRODUCT(--($I$1:$I$527={2001,2002,2003})*($H$1:$H$5275))

"Monty" wrote:

cheers for this, one more thing how can i add more cost centres to the first
line for example:-
=sumproduct(--($I$1:$I$527=2001,2002,2003),--($H$1:$H$5275))

Thanks again

Monty


"Toppers" wrote:

Try:

=sumproduct(--($I$1:$I$527=2001),--($H$1:$H$5275))

Sumproduct must have arrays (i.e. cannot be columns e.g H:H) and they must
be of same size.

HTH

"Monty" wrote:

Dave

Thanks for this See below for what i am trying to acheive.

In column (I) I have cost centres, which range from 2001 to 2042 and in
column (H) I have the number of days taken for payment. In cell AA4 and I
want to return all payments over 5 days belonging to cost centres
2033,2036,2037 & 2041.

Can you please help.

Monty

"davesexcel" wrote:


Something like this??

=COUNTIF($I$1:$I$527,2001)&" and
"&COUNTIF(H:H,"5")-COUNTIF(H:H,"<0")


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=564611