Excel Function - SUMIF cells with specific formats

  • Thread starter Thread starter edufaria
  • Start date Start date
E

edufaria

Hello,

I have a set os values in a column with different formats and I woul
like to sum the values of the cells which have a specific format.

For exemple, I have some of the cells with the CUSTOM format
"€#,##0.00_);(€#,##0.00)" and other ones with the CUSTOM forma
"¥#,##0.00_);¥#,##0.00)"

Is there any way that I can sum only the cells with the first CUSTO
format using the function SUMIF??

Thank you very much,
Eduardo Fari
 
I think this would need a UDF - your own function written in VBA as the Cells("format",ref) will not work due to the formats being too simila

something like this should wor

function mySum(rng as range
dim chkSum as doubl
application.volatil
chkSum =
for each c in rn
if c.numberformat = "€#,##0.00_);(€#,##0.00)" the
chkSum = chkSum + c.valu
end i
nex
mySum = chkSu
end functio

To use this, from XL, press ALT+F11 - this takes you to the VB
Go Insert>Module and copy / paste the code above in ther

In your worksheet, you should now be able to ente
=mySum(A1:A100

and it will sum up the values in A1:A100 where the format matche

Hope this help
Geoff
 
I am trying to change your function to count only the cell which conten
is bold, I am not sure how to do it correctly, it is still not working
can you help me please? thanks a lo
 
Hi
try

function mySum_bold(rng as range)
dim chkSum as double
application.volatile
chkSum = 0
for each c in rng
if c.font.bold
chkSum = chkSum + c.value
end if
next
mySum_bold = chkSum
end function

Frank
 
Back
Top