Get SUM IF format equals

W

WarHorse

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
to
 
B

Bob Phillips

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

You can count them with SUMPRODUCT, like so

=SUMPRODUCT(--(CellPatterns(F1:F18)=CellPatterns(G1)))

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
Else
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


--

HTH

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

Top