Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Sumifs statement with one criteria looking at multiple columns to sum

Posted on 2013-06-11
Medium Priority
331 Views
I have a spreadsheet with multiple columns. In columns B, E and H, I have my payment type. In columns C, F and I are the payment amounts. I need to sum to total payments made by payment type. See attached spreadsheet as example. The formula in L5 should total \$1,100, L6 should total \$1,325, etc. How would i do this? Thanks!
Example.xlsx
0
Question by:brasiman

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 1400 total points
ID: 39239599
Use this formula:

=SUMPRODUCT((B7:H11="Visa")*1,C7:I11)

Kevin
0

LVL 81

Expert Comment

ID: 39239603
See attached.

Kevin
Example.xlsx
0

LVL 19

Assisted Solution

Raheman M. Abdul earned 300 total points
ID: 39239608
Total visa = =VLOOKUP(K5,\$B\$7:\$C\$11,2)+VLOOKUP(K5,\$E\$7:\$F\$11,2)+VLOOKUP(K5,\$H\$7:\$I\$11,2)
0

LVL 50

Assisted Solution

barry houdini earned 300 total points
ID: 39240526
You can use SUMIF, i.e. this formula in L5 copied down

=SUMIF(B\$7:H\$11,K5,C\$7:I\$11)

regards, barry
0

Author Closing Comment

ID: 39282557
They all worked. Thanks for the example too Kevin!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month15 days, 17 hours left to enroll