Get SUM IF format equals



Does anyone have any suggestions on how I might accomplish this?

I have a spreadsheet with about 300 rows and 20 columns. I need t
figure out a way to get a sum of all the numbers in a column an
exclude the cells that have a pattern applied to them.

The column is plain other than the pattern, no colors, just white.

I use the pattern to show when a payment has been received.
I can change the format to anything that will make it work if I nee



Bob Phillips

Here's a small UDF that returns an array of patterns.

You can count them with SUMPRODUCT, like so


where F1:F18 is the range to be tested, and G1 holds the pattern you wish to
check for

Function CellPatterns(rng As Range) As Variant
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryStyle As Variant

If rng.Areas.Count > 1 Then
CellPatterns = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then

aryStyle = rng.Interior.Pattern
aryStyle = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1
aryStyle(i, j) = cell.Interior.Pattern
Next cell
Next row
End If

CellPatterns = aryStyle

End Function



Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads