Find Sum with Citeria
 

Dear All,

Kindly assist me with a table that i needed to count a sum with citeria

example:

John's: Day1 Day2 Total
Apples 50 100 150
Oranges 80 150 230
Papaya 100 50 150

Mark's
Oranges 150 50 200
Apples 100 50 150
Papaya 80 80 160

So on.........................

total Apples: 300
total Oranges: 430
total Papaya: 310

As there are hundreds of Names, i wonder whether there are a functions that
that "apples" as ref and count the total sum of it.

Find Sum with Citeria
 

one way:

=SUM(IF(A1:A100="Apples",D1:D100,0))

CTRL+SHIFT+ENTER this formula as this is an array-formula


On 14 Sty, 10:26, Will wrote:
Dear All,

Kindly assist me with a table that i needed to count a sum with citeria

example:

John's: * * * * * * * * * * * * *Day1 * * * * * * Day2 * * * * Total
* * * * * * * Apples * * * * * * * 50 * * * * * * *100 * * * * * *150
* * * * * * * Oranges * * * * * *80 * * * * * * * 150 * * * * * *230
* * * * * * * Papaya * * * * * * *100 * * * * * * 50 * * * * * * 150

Mark's * * * * * * * * *
* * * * * * * Oranges * * * * * *150 * * * * * * 50 * * * * * * 200
* * * * * * * Apples * * * * * * *100 * * * * * * *50 * * * * * * 150
* * * * * * * Papaya * * * * * * *80 * * * * * * * 80 * * * * * * 160

So on.........................

total Apples: * *300
total Oranges: *430
total Papaya: * *310

As there are hundreds of Names, i wonder whether there are a functions that
that "apples" as ref and count the total sum of it.

Find Sum with Citeria
 

If the fruit list is in column B and the Total is in column E then something
like this would work

=SUMIF(B2:B500,"apples",E2:E500)


"Will" wrote:

Dear All,

Kindly assist me with a table that i needed to count a sum with citeria

example:

John's: Day1 Day2 Total
Apples 50 100 150
Oranges 80 150 230
Papaya 100 50 150

Mark's
Oranges 150 50 200
Apples 100 50 150
Papaya 80 80 160

So on.........................

total Apples: 300
total Oranges: 430
total Papaya: 310

As there are hundreds of Names, i wonder whether there are a functions that
that "apples" as ref and count the total sum of it.

Find Sum with Citeria
 

=SUMPRODUCT((B2:B200="Apples")*(C2:E200))

--
__________________________________
HTH

Bob

"Will" wrote in message
...
Dear All,

Kindly assist me with a table that i needed to count a sum with citeria

example:

John's: Day1 Day2 Total
Apples 50 100 150
Oranges 80 150 230
Papaya 100 50 150

Mark's
Oranges 150 50 200
Apples 100 50 150
Papaya 80 80 160

So on.........................

total Apples: 300
total Oranges: 430
total Papaya: 310

As there are hundreds of Names, i wonder whether there are a functions
that
that "apples" as ref and count the total sum of it.