1

I have a huge data set (with between 300 000 - 400 000 observations) in Excel. I now want to make a table that sums values from the data set based a certain criteria, like year and country. For that purpose I use sumif:

=SUMIFS('Data'!F:F; 'Data'!$A:$A;$A2)

Where $A2 is the country (I have four more input criteria, B1-E1, that are not shown here).

This works fine, except for the problem that I want the code to ignore empty criteria. That is, if I do not put in a value for country, I want it to sum the data for all countries and ignore the empty criteria.

Any suggestions?

edit: See table below for the criteria (A1-E1):

Land    Region    Code  Name of product Year
Sweden  Stockholm 52    Apple           1995
KGB91
  • 630
  • 2
  • 6
  • 24

1 Answers1

0

Try,

=SUMIFS('Data'!F:F; 'Data'!$A:$A; if($A2=""; "*"; A2))   'all column A including blanks
'alternate
=SUMIFS('Data'!F:F; 'Data'!$A:$A; if($A2=""; "<>"; A2))   'all column A except blanks
  • Thanks! Worked fine. I'll accept your solution as soon as I can. – KGB91 Sep 13 '18 at 11:37
  • One more question: how will the code look like if I want to to this for the criteria A1-B1? – KGB91 Sep 13 '18 at 11:38
  • I don't really understand A1-B1 as criteria, nor how blanks come into play. –  Sep 13 '18 at 11:41
  • Edited with the table with the criteria I want to use. Hope it is clearer now! – KGB91 Sep 13 '18 at 11:43
  • 1
    Looks like you would just repeat `'Data'!$A:$A; if($A2=""; "<>"; A2)` four more times for columns B:E. –  Sep 13 '18 at 11:46