SUMIFS formula variation help

[737ngx]

Hello,

I am building a custom tax return file, and I am having problems summing data using multiple selections.
Attached is a file where the formula I have built is in cell F11.

The idea is that this cell should SUM all values in the transactions tab based on the following criteria:
• When the tax code = D11 in the VATSummary! And “Purchase or Sale” in Transaction! = “Sales”

• When the selected item D2 is matched
• When the selected item E2 is matched
• When the selected item F2 is matched

The last 3 items in the drop down list must not be mandatory selected, I like to have the flexibility to report
In a specific month, or just a specific quarter or a specific year, which means that If I select October 2018 and the quarter and Year are blanc
I should only get October 2018, but if I leave Month and quarter blanc but I select under year 2018, than I should get all data for 2018 for tax code in D11 and “Purchase or Sale” in Transactions! = “Sales” ….. If all this makes sense

Any help would be greatly appreciated

Best Regards

[avk]

Try "F11"

Please Login or Register  to view this content.

"g11"

Please Login or Register  to view this content.

[737ngx]

Thanks for the reply but the formulas provided give always a zero value return

[Glenn Kennedy]

Did you replace all of the , with ; you need to as you guys use ; as the separator....

[737ngx]

If I replace commas with semicolon it tells me that there is an error in the formula
I have been using always commas and they work fine.

[José Augusto]

Hi

First correct your Transaction!E2:E4. Year must be converted in numbers.

Another problem is "Sale" <> "Sales" and "Purchase"<>"Purchases" but formula take care of that.

Try this formula in F11:G12

Please Login or Register  to view this content.

and this in F23:G24

Please Login or Register  to view this content.

[737ngx]

I converted the year into a number and pasted the formula provided but I still get zero as a value

[José Augusto]

Hi

I suppose the value must be zero. The filter say that you want Sales but the data is Purchase.
Change Transactions!G2 to "Sale"

[737ngx]

I am re-uploading the file with the changes made, the difference is that I had to change the commas with semicolons because I am in Europe,
but for the rest, it should work but it still not. Can you confirm if you download my file that works?

[José Augusto]

Hi

If you uses "Sales" I do not use LEFT($C$9,4)

The formula if you use "sales"

Please Login or Register  to view this content.

See the file

[737ngx]

I think we are nearly there, the problem is that I seem that I always need to have all 3 dropdown list selected, If I want to have all Sales invoices for the given TAX code for 2018, the formula should pick up 2 records, but it brings back zero

[avk]

Try "F2"

Please Login or Register  to view this content.

"G2"

Please Login or Register  to view this content.

[737ngx]

nope, I am still forced to select a condition in all 3 drop down lists, if I just select in the year 2018, I get nothing or if I just select in the month October 2018 I get nothing....

and BTW... You dod not really mean that those formulas should go in F2 and G2 right?

[avk]

Why not using Pivot Table.
See attachment.

[Glenn Kennedy]

Try this in F11, copied across and down:

=SUMIFS(Transactions!K:K,Transactions!$C:$C,IF($D$2="","<>",$D$2),Transactions!$H:$H,$D11,Transactions!$D:$D,IF($E$2="","<>",$E$2),Transactions!$E:$E,IF($F$2="","<>",$F$2),Transactions!$G:$G,$C$9)

[737ngx]

Yes... Yes... Yes... and did I mention ...yeeeeeeeees?????

Thanks so much, now I'll have to spend some time understanding the formula so I can manipulate it for other tax codes or if I need to add an extra Drop down list.

Thanks again

[Glenn Kennedy]

It's sumple enough....

The criterion is: if the dd cell is blank, count all non blanks in the criterion column, otherwise onlt count those that meet the criteria.