use of array formula inside function

Hello all,

i am quite newbie to excel and i am experimenting with some things , and i have been stuck to one specific thing for a while . I am not sure if i understand correctly and correct me if i am wrong but until now i get that array formula is giving a result of a range of value.
So i am having this issue that i will describe as much as i can :

i am handling an excel sheet that is recording the payments from clients per project. As it happens many times , client will split the payment in more than 1 rounds and that results in a column called partial payments that records the split payments and then these are summed up by project in the column Full Payment. For clients that paid only once per project the amount of payment goes straight to full payment column.

I needed to create a table with amount paid from client per month , so lets say i want to find out how much client A paid during February, i started with something like =SUMPRODUCT(SUMIFS(C3:C10,A3:A10,"A",E3:E10,MONTH(G4))

This works just fine but unfortunately it only sums whatever falls into that criteria from column C . In order to include Sum of projects paid in one payment i could do an additional sumifs function in the sumproduct that would be summing just the amounts of column D that have appropriate date. As you can see when a project is split to partial payment the Full Payment amount representing this project does not have a date of payment related to that sum so there would be no duplicates summing up .

However i was looking into creating a range with IF function that would be doing the following : =If(C3=0, D3,C3) . So for every empty cell i would get from partial payment column , i would use the value of Full payment cell. Even though that works in a single cell it has to be used as an array in order to get a range of values, and that is it =ArrayFormula(if(C3:C10=0,D3:D10,C3:C10))

My question now is , can i use this array formula and nest it inside a sumproduct function ?

so it would be =SUMPRODUCT(SUMIFS(ArrayFormula(if(C3:C10=0,D3:D10,C3:C10)),A3:A10,"A",E3:E10,MONTH(G4)))

When i try to make it work i get an error , so i am understanding that the array formula is not giving a result of a range in the function and propably is giving just the very first result of the array or it cannot understand at all what an array is doing there.

If you have some insight on how this could be solved please i would be grateful to hear any suggestions.

For now i have solved it by using the simple IF function (=If(C3=0, D3,C3)) i mentioned above to create a totally separate column of all the values in place and then use that column as a range in my sumproduct function.

I am interested mostly for educational purposes on how and if this could be at all solved with array inside another function .

Thank you very much for your time and your patience with my super long and maybe complicated explanation.

Looking forward to any comments .
Thank you ,
Sofi

PS i know this screenshot is from google sheets , it was the only thing i could find fast to make the screenshot from , i regularly use excel 2010 .

SUMPRODUCT is an array formula that you don't have to press CLTR-ALT-ENTER to enter. It could be replace with SUM entered as an array formula. So I don't think you need to nest SUM and SUMPRODUCT.

It would help a lot if you could include a workbook with some sample data. It might even be that with the help of some helper columns a pivot table would work.

At any rate, the following gives some guidance on array formulas: http://www.utteraccess.com/wiki/inde...Array_Formulas