writing a customised UDF like SUMIFS

[Qroozn]

Hey everyone.
I have been trying to utilise the SUMIFS function, but have found it is limited in achieving what I want (e.g. - I have 8 conditions - and i think SUMIFS only does upto 5, and i also want to have a sub CONDITION to each condition).
e.g. I only want condition 1 to run if $A$1 (on the current page) <> 1, and i only want condition 2 to run if $A$2 (on the current page) <> 1.

I think the best solution is to write a UDF that i could build this logic into.

has anyone got a UDF that would replicate the current SUMIFS function that i could modify to handle my enhancements?

Thanks in advance.

 

[jasonb75]

I have 8 conditions - and i think SUMIFS only does upto 5

You can have up to 127 conditions. For 'sub conditions' as you call them, you probably just need to use IF in the SUMIFS criteria.

For example, this would sum column A if column B = "Qroozn" and $A$1<>1. IF $A$1=1 then it will sum with anything in column B

=SUMIFS(Sheet2!A:A,Sheet2!B:B,IF($A$1<>1,"Qroozn","*"))

The method for the last part varies depending on the criteria, text and numbers need to be handled differently, possibly blank and empty cells would as well.