Devide two "SUM.IF" formulas

[Scarface]

Situation:

Sheet A: Dates in cells A2:A1000
Sheet A: Actions in cells B2:B1000
Sheet A: Error in cells C2:C1000

Sheet B: Date1 in cell D1
Sheet B: Date2 in cell E1

I would like to calculate an error percentage of actions if the dates are between the two dates in Sheet B, D1 and E1.

I tried the following formula, but without succes

=SUM.IF(Sheet A!A2:A1000 >= D1;Sheet A!A2:A1000 <= E1;Sheet A!C2:C1000)/Sheet A!A2:A1000 >= D1;Sheet A!A2:A1000 <= E1;Sheet A!C2:C1000)

Thanks in advance

 

[Andrew Poulsom]

You haven't said what sort of data you have in columns B and C. If they are numbers try:

=SUMPRODUCT(--('Sheet A'!A2:A1000>=D1);--('Sheet A'!A2:A1000<=E1);'Sheet A'!C2:C1000)/SUMPRODUCT(--('Sheet A'!A2:A1000>=D1);--('Sheet A'!A2:A1000<=E1);'Sheet A'!B2:B1000)

 

[Scarface]

You haven't said what sort of data you have in columns B and C. If they are numbers try:

=SUMPRODUCT(--('Sheet A'!A2:A1000>=D1);--('Sheet A'!A2:A1000<=E1);'Sheet A'!C2:C1000)/SUMPRODUCT(--('Sheet A'!A2:A1000>=D1);--('Sheet A'!A2:A1000<=E1);'Sheet A'!B2:B1000)

It says "#Devide/0"

Furthermore, the values in B and C are numbers

 

[Andrew Poulsom]

The formula worked when i tested it. It will return #DIV/O! if there are no entries between those dates. Would you like to post some sample data and the expected result?

 

[Scarface]

Sorry for the delay in my reaction, but it works perfectly! Also, i added another condition:

=SOMPRODUCT(--('Sheet A!C3:C4998>=Sheet B!A38);--('Sheet A'!C3:C4998<=Sheet B!B38);--('Sheet A'!B3:B4998=Sheet B!A11);'Sheet A'!O3:O4998)/SOMPRODUCT(--('Sheet A'!C3:C4998>=Sheet B!A38);--('Sheet A'!C3:C4998<=Sheet B!B38);--('Sheet A'!B3:B4998=Sheet B!A11);'Sheet A'!N3:N4998)

Is this the correct way to do this?

 

[Andrew Poulsom]

Your formula looks OK.