Summing Filtering Problem

[vchibisov]

I want to count up the OP hours for physician A
I have a formula that works in some cases: (this is from the real document)
It compares the year, day, month individually.
=SUMIFS($G:$G,$A:$A,$A21,$C:$C,$E$1,$D:$D,$A$1,$E:$E,"OP*")


Unfortunately it no longer meets my needs now that I also want an ability to select between which dates I am summing the hrs. I want to use the =Value() function that calculates the numeric value of a date and then I want to drudge my document for lines where its Physician A, Location OP, Dates between Value(A1) and Value(A2) and add up all the hours. I tried using =ProductSum() but that does not seem to be an option with a wildcard string. A sample doc is attached.

Any help would be appreciated.

Best,

[oeldere]

With pivot table (see the attached file).

[vchibisov]

I thought about pivot tables. They do not do the trick because I am generating a number of individual reports and the outcome of my summation goes on to do other things. The idea is to avoid manually dealing with this (otherwise I would just filter).

Thanks,

[oeldere]

I don't see (based on your information) the problem, using pivot table.

But in that case you can use Sumproduct (instead of ProductSum).

[vchibisov]

Sorry about that: I meant sumproduct. Perhaps I do not have that kind of mastery of Pivot tables, but I need a cell, say cell A50, to display the result as my vba code toggles through a list of physicians and changes which one the analysis is being performed for.

[vchibisov]

Is it possible to do sumifs with inequalities (ie ">")

[vchibisov]

=SUMIFS($G:$G,$D:$D,$A$1,$E:$E,("*OP*"),$I:$I,">="&VALUE($G$13),'C:\Users\vchibisov\Desktop\Active Use\[Encounter Productivity Company Wide.xlsm]2D'!$I:$I,"<="&VALUE($G$14))