ExcelBanter - formula that compares different ranges

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - formula that compares different ranges (https://www.excelbanter.com/excel-worksheet-functions/55272-formula-compares-different-ranges.html)

sir Lancelot

formula that compares different ranges

I need a formula that finds criteria in a range if a different range contains
text data.
I already use countif
EXAMPLE:
=countif(g4:g51,".")
now I need that formula to work only if (D4:D51) contains text data.

Peo Sjoblom

formula that compares different ranges

=SUMPRODUCT(--(ISTEXT(D4:D51)),--(G4:G51="."))

or if you by text means cells hold anything but blank

=SUMPRODUCT(--(D4:D51<""),--(G4:G51="."))

--
Regards,

Peo Sjoblom

(No private emails please)

"sir Lancelot" wrote in message
...
I need a formula that finds criteria in a range if a different range
contains
text data.
I already use countif
EXAMPLE:
=countif(g4:g51,".")
now I need that formula to work only if (D4:D51) contains text data.

sir Lancelot

formula that compares different ranges

Thank you, Very helpful, have been struggling over this for the past couple
of days. problem solved.

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(ISTEXT(D4:D51)),--(G4:G51="."))

or if you by text means cells hold anything but blank

=SUMPRODUCT(--(D4:D51<""),--(G4:G51="."))

--
Regards,

Peo Sjoblom

(No private emails please)

"sir Lancelot" wrote in message
...
I need a formula that finds criteria in a range if a different range
contains
text data.
I already use countif
EXAMPLE:
=countif(g4:g51,".")
now I need that formula to work only if (D4:D51) contains text data.

All times are GMT +1. The time now is 08:14 PM.