Double If Count

[XCESIV]

Good morning everyone

Just for a little question here

I have a worksheet of raw data here.
I have a few formulas i need to calculate using 2 criterias.
I beleive the formulas will all be very similar.

2 worksheets
* Data
* Report


Forumula 1)
I am trying to count the total transactions that meet both criteria

Count
If DATA Culumn D = REPORT Column A
and
If DATA Culumn B = "IN"

Forumula 2)
Find the Max time duration in DATA Column G with both criteria being met.

Forumula 2)
Find the Average time duration in DATA Column G with both criteria being met.

Thank you in advance for your help.

Warren

[XCESIV]

Im no good at the sumproduct feature

Is this correct for formula 1

=SUMPRODUCT((Data!D1:D65000=Report!$A$2)+(Data!B1:B65000="inbound"))
Thanks

I have no idea wbout Furmula 2 and 3 tho.

[martindwilson]

for count
=SUMPRODUCT(--(report!A1:A10=data!D1:D10),--(B1:B10="in"))
for max
=MAX((report!A1:A10=data!D1:D10)*(data!B1:B10="in")*(data!G1:G10))
array entered with ctrl+shift+enter
see link in my sig how to enter arrays
for average
=SUMPRODUCT(--(report!A1:A10=data!D1:D10),--(data!B1:B10="in"),(data!G1:G10))/SUMPRODUCT(--(report!A1:A10=data!D1:D10),--(data!B1:B10="in"))

[DonkeyOte]

Without knowing exactly what the report range values are in A1:A10 etc it might make sense to use a Pivot Table ?

Column B as PAGE Field
Column D as ROW Field
Set Time Duration as DATA Field twice - once set to MAX and the other to AVERAGE.

This will be a more effective approach going forward.