Formula help

Topic

New Reply

A few formula questions, as it’s late and my brain has switched off….

=SUMPRODUCT((‘Daily Files’!$A$2:$A$20000=”H”)*(‘Daily Files’!$H$2:$H$20000=”SOL”),(‘Daily Files’!$I$2:$I$20000))

This gives me the sum, how do I get the count?

=COUNTIF(‘Payment Files’!E:E,”H”)
=SUMIF(‘Payment Files’!E:E,”H”,’Payment Files’!I:I)

In both of these, I need to add a further condition to only count/sum the figures in I that are positive?

Thanks

Reply | Quote

Replies

To get the count of rows meeting both conditions:

=SUMPRODUCT((‘Daily Files’!$A$2:$A$20000=”H”)*(‘Daily Files’!$H$2:$H$20000=”SOL”))

To count the number of rows where column E contains “H” and column I contains a positive number:

=SUMPRODUCT((‘Payment Files’!E1:E20000=”H”)*(‘Payment Files’!I1:I20000>0))

And to sum the numbers in column I with these conditions:

=SUMPRODUCT((‘Payment Files’!E1:E20000=”H”)*(‘Payment Files’!I1:I20000>0),’Payment Files’!I1:I20000)

You can’t use SUMPRODUCT with entire columns.

Reply | Quote

This should do it:

=SUMPRODUCT((‘Daily Files’!$A$2:$A$20000=”H”)*(‘Daily Files’!$H$2:$H$20000=”SOL”)*1)

Reply | Quote

Thankyou both. I can get to bed now……

Reply | Quote