Want Modification in MAX and COUNTIF formulas

[Anzar]

bro ChemistB did this attempt, it worked perfectly, but i want a modification,
i want this formula to be modified in such a way that if information is not in

1: One table or
2: one sheet.

then how can you change this formula

the formula was
=MAX(--(Sheet1!$C$2:$C$180=A26)*Sheet1!$E$2:$E$180)

In The new attached book, the formula is used in the sheet "Voice Call Analysis & SEARCH ", the formula is applied on the table in Sheet "Invoice Details",

Requirement:
I want the formula to be applied on the tables of Shhet" Itemized Call Details",
I hope i ve made my requirement clear..

and can it be also done with Countif formula in the same book
=COUNTIF('Invoice Details'!$C$2:$C$180,B17)


His post--( ChemistB)-- was as follows.....
"
Here's my attempt.

I used the following Array formula in Sheet 2 Cell C26

Code:
=MAX(--(Sheet1!$C$2:$C$180=A26)*Sheet1!$E$2:$E$180)



and then dragged it down to cover all phone numbers.
Note: Array formulas must be entered by CNTRL SHFT ENTER

Explaination:
Sheet1!$C$2:$C$180=A26 looks at each row and returns true or false if it finds that phone number in that row. The "--" before it converts True into 1 and False into 0.

It multiplies this number (0 or 1) * the duration in the same row (column E). Because it is an array formula, it stores each of these values (1 per row) in memory and then determines which is the maximum (MAX) value and returns that.

You initially had the phone numbers on Sheet 1 as text and on sheet 2 as numbers. I made them all text so this would work."

[Anzar]

i hope to get a solution soon....






.

[Anzar]

the same file but in 97-2003 files.

[Anzar]

the same file but in 97-2003 files.

[Anzar]

the same file but in 97-2003 files.

[all4excel]

I wud defintely try and help u , however can u pls explain exactly wht u r looking out for as ur question does not help me decipher that?

[mikerickson]

I'd try replacing Sheet1! with 'Voice Call Analysis & SEARCH'! throughout that formula.

[Bjornar]

I'm not sure if I understand you right, but if you want your formula to look in the 3 tables in sheet "Itemized Call Details" instead the 1 table in "Invoice Details" try replacing this formula

=MAX(--('Invoice Details'!$C$2:$C$180=H18)*'Invoice Details'!$E$2:$E$180

with this formula:

=MAX(
MAX(-- ('Itemized Call Details'!$D$4:$D$57=H18)   * 'Itemized Call Details'!$F$4:$F$57),
MAX(-- ('Itemized Call Details'!$D$62:$D$95=H18) * 'Itemized Call Details'!$F$62:$F$95),
MAX(-- ('Itemized Call Details'!$C$99:$C$214=H18) *'Itemized Call Details'!$E$99:$E$214) )

I have added som spaces and some linefeed (ALT + ENTER) to make the formula more readable. That is perfectly legal to do in Excel formulas and can help you a lot when the size of the formula grows.

I'm not sure why you would want to change this formula, as both gives you the same result. That is because the 3 tables in sheet "Itemized Call Details" are based on the data in "Invoice Details"

It is stil an array formula so remember CTRL + SHIFT + ENTER