sumifs with name

[F8MS]

I have defined a name let's say Accounts which is A1, A2, A3


In column A I have all list of accounts A1 to A10 and B1 to B10 (range A2 to A50)

In column B I have the related values (range B2 to B50)

In column C I have the related dates (range C2 to C50)


I need to sum the value of what I defined Accounts in names for a selected period, let's say from 01.08.12 to 31.08.12.


What is the syntax to include the name 'Accounts' int he sumif formula?


Thank you

 

[Luke M]

I'm not sure of your ranges as it got a little confusing, but I think the syntax is something like:

=SUMPRODUCT((B2:B50)*(C2:C50>=StartDate)(C2:C50<=EndDate)*((A2:A50=Name1)+(A2:A50=Name2)+(A2:A50=Name3)))

 

[F8MS]

Thank you

i manage to get it work, although the formula doesn't work using Name but I have to specify the value.

In my example Name1 was setup taking 3 values A1, A2, A3

i did try

=SUMPRODUCT((B2:B50)*(C2:C50>=StartDate)(C2:C50<=EndDate)*(A2:A50=Name1))

but didn't work,

so I had to do it

=SUMPRODUCT((B2:B50)*(C2:C50>=StartDate)(C2:C50<=EndDate)*((A2:A50=A1)+(A2:A50=A2)+(A2:A50=A3)))

It works but doesn't give me the flexibility If i want to add an additional data to my name1

 

[Luke M]

If you have a range of names, you could do:

=SUMPRODUCT((B2:B50)*(C2:C50>=StartDate)(C2:C50<=EndDate)*(ISNUMBER(MATCH(NameList,A2:A50,0))))