New Challenge - Evertime It Matchs 2 Criteria Add the number

[Superstar31]

Ok Here is my new dilemna....

I need to add up the number associated with R3 every time it appears.

Kinda like if a9=r3 then add the number in b9

and there is more

if a10=r3 then add the number of b10 to the number in b9

and if a12=r3 then add the number in b12 to our running total

so i want to total the amount of every time r3 appears in column a

Hopefully that makes sense

----------------- (-----A-----) (---B---) (----C-----) (-----D-----) (-------E-------)
(-------1-------) (-----CP-----) ($45.00) (---Pizza---) (--Canvas--) (-------1-------)
(-------2-------) (-----35-----) ($34.03) (---Pizza---) (--Canvas--) (-------1-------)
(-------3-------) (-----AC-----) ($25.00) (---Pizza---) (--Canvas--) (-------1-------)
(-------4-------) (-----93-----) ($3.66) (---Pizza---) (--Canvas--) (-------1-------)
(-------5-------) (-----80-----) ($13.62) (---Pizza---) (--Canvas--) (-------1-------)
(-------6-------) (-----93-----) ($10.00) (--Candle--) (---Box---) (-------5-------)
(-------7-------)
(-------8-------)
(-------9-------) (-----R3-----) ($15.00) (---Soda---) (---Wall---) (-------2-------)
(------10-----) (-----R3-----) ($101.90) (---Soda---) (---Wall---) (-------2-------)
(------11-----)
(------12-----) (-----R3-----) ($50.00) (---Soda---) (---Wall---) (-------2-------)
(------13-----) (-----R3-----) ($40.00) (--Candle--) (---Box---) (-------5-------)
(------14-----) (-----R3-----) ($4.90) (---Pizza---) (--Canvas--) (-------1-------)
(------15-----) (-----21-----) ($15.00) (-Root Beer-) (---Staple---) (-------3-------)
(------16-----) (-----R3-----) ($65.18) (--Lettece--) (---Phone---) (-------4-------)
(------17-----) (-----35-----) ($29.50) (--Lettece--) (---Phone---) (-------4-------)
(------18-----) (-----R3-----) ($30.00) (--Candle--) (---Box---) (-------5-------)
(------19-----)
(------20------) (-----R3-----) ($65.30) (---Pizza---) (---Canvas---) (-------1-------)



Thanks and have a great weekend

 

[erik.van.geit]

Hi,
you can use something like this

=SUMPRODUCT(--(A2:A65536="r3"),B2:B65536)

to post examples use Colo's HTML Maker utility for displaying your Excel Worksheet on the board

On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.
On the main page of the Forum there is a link to a Test here forum and a link to About this Board - to ask questions about HTLMMaker ...

kind regards,
Erik

 

[Aladin Akyurek]

Faster...

=SUMIF($A$1:$A$20,K1,$B$1:$B$20)

where K1 houses R3 as criterion.

 

[erik.van.geit]

Hi, Aladin,

although computers are increasing in speed and power, the "runtime" is always a concern of me

would this be a good test ?
filling entire column (or more?) with same formula
using code to calculate the sheet 1000 times
starttime = Timer
loop to calculate
MsgBox Timer - starttime

best regards,
Erik

 

[Aladin Akyurek]

Hi, Aladin,

although computers are increasing in speed and power, the "runtime" is always a concern of me

would this be a good test ?
filling entire column (or more?) with same formula
using code to calculate the sheet 1000 times
starttime = Timer
loop to calculate
MsgBox Timer - starttime

best regards,
Erik

Erik,

I suppose so. I use FastExcel of Charles Williams for temporal profiling.

However, I like to add that we don't need that here. SumIf operates on range objects, SumProduct on array objects (computed arrays). Operating on ranges is always faster than on computed arrays. Thus: SumIf wins.

 

[erik.van.geit]

Aladin,
thank you for taking the time to reply to my question

However, I like to add that we don't need that here. SumIf operates on range objects, SumProduct on array objects (computed arrays). Operating on ranges is always faster than on computed arrays. Thus: SumIf wins.

I didn't doubt a second on your first reply

Faster...

it was just a question
I will create a little code to check things.

Operating on ranges is always faster than on computed arrays.

in VBA it's the inverse. Seems rather logic to "stay where you are". Formulas with worksheetfunctions and VBA using memory...

very kind regards,
Erik

EDITl changed some "stupid" typos

 

[Superstar31]

OK, that worked better than I thought, I ended up removing a whole workbook with that one Thanks

But here is my next problem

=SUMIF(Credits!$D$2:$D$1101,A2,Credits!$E$2:$E$1101)

However I want to add a "AND" statment in there

is that possible, and how do i do it?

I want the and statement to compare 2 other paramenters.

Completed Formula

=SUM(IF((Credits!$D$2:$D$1101=$A32)*(Credits!$H$2:$H$1101=G$1),Credits!$E$2:$E$1101,0))

 

[Aladin Akyurek]

OK, that worked better than I thought, I ended up removing a whole workbook with that one Thanks

But here is my next problem

=SUMIF(Credits!$D$2:$D$1101,A2,Credits!$E$2:$E$1101)

However I want to add a "AND" statment in there

is that possible, and how do i do it?

I want the and statement to compare 2 other paramenters.

Completed Formula

=SUM(IF((Credits!$D$2:$D$1101=$A32)*(Credits!$H$2:$H$1101=G$1),Credits!$E$2:$E$1101,0))

Either:

=SUMPRODUCT(--(Credits!$D$2:$D$1101=$A32),--(Credits!$H$2:$H$1101=G$1),Credits!$E$2:$E$1101)

Or:

=SUM(IF(Credits!$D$2:$D$1101=$A32,IF(Credits!$H$2:$H$1101=G$1,Credits!$E$2:$E$1101)))

which must be confirmed with control+shift+enter, not just with enter.

Or: Construct a pivot table from your data.

 

[erik.van.geit]

Hi, I don't know of a solution for this using SUMIF
try this syntax
=SUMPRODUCT(--(A2:A65536=A1),--(B2:B65536=B1),C2:C65536)
which is an enlargement of the formula you received earlier

the formula is multiplying three arrays, then summing up the results

A       B       C
0   *   1   *   20
1   *   1   *   10
0   *   0   *   40
1   *   0   *   23
1   *   1   *   12
(0 means not equal to criterion)

result: 0 + 10 + 0 + 0 + 12 = 22

kind regards,
Erik

 

[Superstar31]

Sorry, When I said completed formula, I meant that was the formula I used to complete it.

and yes you are correct, (ctrl, shift, enter) must be used