SUMIFS - what criteria should I use to sum all number values

[manchoon]

Hi
can anyone please help me?

I have numeric values in column A (sales), Seller ID codes in column B (1,2,3...), Shop ID codes in column C (1,2,3...). In cells E1 and F1 I have list (data validation) of criterias to use in sumifs formula. For example if I need sales of seller 2 and shop 3, E1=2 F1=3 and =SUMIFS(A:A,B:B,E1,C:C,F1)

but my problem is that when I need to calculate for example sales of seller 2 and all shops, I don't know what to write in F1, wildcard * doesn't work because it only works with text values.

what should I do? is there another way to get this result maybe with another formula?
please help

 

[Airfix9]

=IF(ISBLANK(F1),SUMIF(B:B,E1,A:A),SUMIFS(A:A,B:B,E1,C:C,F1))

Leave F1 blank if you want all sales for seller 2.

 

[manchoon]

Thank you! worked great ^_^

may I ask you one more thing?
If I also have column D with addresses (also ID numbers) and I want to sum for example, (sales of seller 2, all shops, all addresses) or (all sellers, all shops, all addresses) or (all sellers, 2nd shop, all addresses) and so on.. again I switch criteria with data validation list. for each criteria cell there are 2 kind of criterias - nothing or ID code. Like this there are too many criteria possibilities (8) and IF function will probably be too complex and long right?
is there any way to do it? :/

 

[Airfix9]

Assuming that shop address to search for is in G1 and that all numbers are numeric and greater than zero:

=SUMIFS(A:A,B:B,IF(ISBLANK(E1),">0",E1),C:C,IF(ISBLANK(F1),">0",F1),D:D,IF(ISBLANK(G1),">0",G1))