Sum Amount according to two column text-CodePudding

I don't understand how I explain this

What formula I used to sum amount according to two column text "Name & Pending", if status is pending than amount add to the same pending column but also match the name column too

picture will help you better

thanks in advance

CodePudding user response：

This is the exact reason why SumIf() worksheet function has been invented.
Try using this function:

=SUMIF(A$2:A$13,A3,B$2:B$13)

(You can drag this function down for all six names.)

It uses following parameters:

A$2:A$13 : those are the values who will be checked. They need to be fix, hence the absolute cell references.
A3 : the values against which to be checked. As this will vary, it should be a relative cell reference.
B$2:B$13 : those are the values who will be summed together.

If you drag the formula down for six rows, this is what you'll get (with their values):

=SUMIF(A$2:A$13,A2,B$2:B$13) => 3
=SUMIF(A$2:A$13,A3,B$2:B$13) => 6
=SUMIF(A$2:A$13,A4,B$2:B$13) => 9
=SUMIF(A$2:A$13,A5,B$2:B$13) => 12
=SUMIF(A$2:A$13,A6,B$2:B$13) => 15
=SUMIF(A$2:A$13,A7,B$2:B$13) => 18

In a screenshot:

There is also the possibility to add more criteria, using the =SubIfS() worksheet function, for which there is this example:

=SUMIFS(B$2:B$13,A$2:A$13,A2,C$2:C$13,C2)

Meaning the following:

B$2:B$13 : you need to take the sum of those values
A$2:A$13,A2 : you only take the sum where the value of A$2:A$13 equals A2
C$2:C$13,C2 : ... and you only take the sum where the value of C$2:C$13 equals C2

You can use the first SumIf() and subtract the second SumIfS() from it, but this, obviously, is not very clear, hence this second screenshot:

The formula:

=SUMIF(A$2:A$13,A2,B$2:B$13)-SUMIFS(B$2:B$13,A$2:A$13,A2,C$2:C$13,C2)

Meaning: first take the sum of all amounts where the name is equal to "John" (A2), and from that, subtract the sum of all amounts where the name is equal to "John" and the Status is equal to "Pending" (C2).