Combining SUMPRODUCT and DATE functions

[Hasko]

Guys,

I have a table where I fill-in various info:



I would like to focus on the last three column headers/ date/month/year (used formula to extract month and the year from the date)

Then on another sheet I have new tables where I sum the information by "persons, companies etc..." only when specific conditions have been met using SUMPRODUCT, here's the other sheet (look at the formula bar to see the actual formula I use):



Now, is there a way not to use =MONTH() and =YEAR() (delete last two columns completely) functions but to somehow combine these with the existing SUMPRODUCT and meet date related conditions only from the date column (save space on the sheet and make it more straight-forward), current formula e.g.:

=SUMPRODUCT(--(Table1[year]=2002),--(Table1[Name]="Test"),--(Table1[month]=1),Table1[iznos izdatih faktura])

instead of using refs to "year" and "month" to find specific results can I combine some other function with sumproduct to e.g. calculate "broj izdatih faktura" for 11/2002 WITHOUT using the separate columns/ last two on the right...???

Guess I was understandable enough

Thx!

 

[Jon von der Heyden]

Try:

=SUMPRODUCT(--(TEXT(Table1[date],"mm-yy")="01-02"),--(Table1[Name]="Test"),Table1[iznos izdatih faktura])

 

[Hasko]

Thx!

goes without saying that it works