SUMPRODUCT HELP

[Sasikiran]

Dear,

I am using the below sumproduct formula to get the necessary result. In this
im getting the count of the rows which fall under the specified time slot
with text as Bangalore in N column and value equal to 0 in G column and value
greater than or equal to 1 in column H.

Now i require to count the sum of the values in column H along with the
other specifications as mentiioned.

For suppose there are two rows which come under this time slot having the
text as Bangalore in N column and value equal to 0 in G column, having the
values as 5 and 6 in column H, I would require the result as 11 (which is the
sum of values 5&6)

Please help.

=SUMPRODUCT(('Monitoring Report'!B$2:B$2999>=--"0:00:01 AM")*('Monitoring
Report'!B$2:B$2999<=--"0:29:59 AM")*('Monitoring
Report'!N$2:N$2999="Bangalore")*('Monitoring
Report'!G$2:G$2999=--"0")*('Monitoring Report'!H$2:H$2999>=--"1"))

 

[Jacob Skaria]

Try the below

=SUMPRODUCT(
('Monitoring Report'!B$2:B$2999>=--"0:00:01 AM")*
('Monitoring Report'!B$2:B$2999<=--"0:29:59 AM")*
('Monitoring Report'!N$2:N$2999="Bangalore")*
('Monitoring Report'!G$2:G$2999=--"0"),
'Monitoring Report'!H$2:H$2999)

OR if you have negative values in Col H

=SUMPRODUCT(
('Monitoring Report'!B$2:B$2999>=--"0:00:01 AM")*
('Monitoring Report'!B$2:B$2999<=--"0:29:59 AM")*
('Monitoring Report'!N$2:N$2999="Bangalore")*
('Monitoring Report'!G$2:G$2999=--"0")*
('Monitoring Report'!H$2:H$2999>0),
'Monitoring Report'!H$2:H$2999)

If this post helps click Yes

 

[Sasikiran]

Thank you so much

Try the below

=SUMPRODUCT(
('Monitoring Report'!B$2:B$2999>=--"0:00:01 AM")*
('Monitoring Report'!B$2:B$2999<=--"0:29:59 AM")*
('Monitoring Report'!N$2:N$2999="Bangalore")*
('Monitoring Report'!G$2:G$2999=--"0"),
'Monitoring Report'!H$2:H$2999)

OR if you have negative values in Col H

=SUMPRODUCT(
('Monitoring Report'!B$2:B$2999>=--"0:00:01 AM")*
('Monitoring Report'!B$2:B$2999<=--"0:29:59 AM")*
('Monitoring Report'!N$2:N$2999="Bangalore")*
('Monitoring Report'!G$2:G$2999=--"0")*
('Monitoring Report'!H$2:H$2999>0),
'Monitoring Report'!H$2:H$2999)

If this post helps click Yes