# SumProduct As SumIfs Criteria

#### bharath

##### Member
Hi,

I am getting result by adding help column
But.. Can't we get the same result by using sumproduct

#### Attachments

• 10.4 KB Views: 11

#### AlanSidman

##### Well-Known Member
Maybe-->=SUMPRODUCT((H7*I7)+(H15*I15)+(H23*I23))

#### bharath

##### Member
Maybe-->=SUMPRODUCT((H7*I7)+(H15*I15)+(H23*I23))
Sorry Sir, My data will be more sir, I just mentioned few records only

#### AlanSidman

##### Well-Known Member
If you are willing to use Power Query, then here is a solution for you. If your data was in a normalized table, then this would be a lot easier and could be resolved using a Pivot Table. I would urge you to change your file layout to make analysis easier.

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filled Down" = Table.FillDown(Source,{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"JAN", "Month"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Month] <> "TOTAL") and ([KILO METERS] <> 0 and [KILO METERS] <> "KILO METERS")),
#"Inserted Multiplication" = Table.AddColumn(#"Filtered Rows", "Multiplication", each [KILO METERS] * [#"COST/KM"], type number),
#"Grouped Rows" = Table.Group(#"Inserted Multiplication", {"Month"}, {{"Total", each List.Sum([Multiplication]), type number}}),
#"Calculated Sum" = List.Sum(#"Grouped Rows"[Total]),
#"Converted to Table" = #table(1, {{#"Calculated Sum"}})
in
#"Converted to Table"``````

#### bharath

##### Member
Hi, I dont know power query
if you solve in excel i will be greatfull to you

Thank you

#### AlanSidman

##### Well-Known Member
I have given you two solutions and have no more for you with the current layout as mentioned earlier as your data is not normalized. Given the current layout, here is information on Power Query

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for a video which demonstrates how to use Power Query code provided.

#### Excel Wizard

##### Active Member
Try

=SUMPRODUCT(H2:H23,I2:I23,--(G2:G23="total"))

#### bharath

##### Member
Excellent
Can't believe

Super it is working....

thanks a lot