Sumif – Multi-Values criteria in Excel IFs

Excel Sumif Formula is very smart for aggregating data, it filter your data prior to perform a calculation. Like Sumif, Excel have some IFs formulas where you supply your criterias and those function can quickly do the calculation on specific dataset as per supplied criterias. SumIf, CountIf, AverageIf, and Sumifs, CountIfs, AverageIfs are the example of those function. You can provide criterias for multiple columns in these function. Normally, you supply only one criteria for one column, but when you need to supply multi-value criteria, you need to provide it as Array. Look at the example given below.

Above, is a table given with countries name and their population (data is taken from wiki) and we need to know the combined population of India, Pakistan and Russia. We can either use three SUMIF and SUM them together or you can Supply all three countries in one go.

=SUMIF(CountryName,Criteria,Population)

when you supply multiple items in criteria in any of IFs function. they returns Array of value which you need to sum later. see the formula below.

=SUMPRODUCT(SUMIF(CountryName,Criteria,Population))

Sumproduct formula will sum the array returned by Sumif function without entering it as Array Function (with CSE).

Join my VBA whatsApp group to know more

Subscribe to get news from IT Chat

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *