sumif neighbouring cell

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

Afternoon all.

Quick question re sumif function.

Spreadsheet has 2 columns. Column A has a transaction type, e.g. Milk,
Bread, Cheese etc. Column B has the number purchased, eg 20, 14, 6 etc.

I would like to count how much of each of these things have been purchased
in the month.
So if Milk is repeated 5 times in the month, how can I add up the total
number of all milks purchased?

Thains in Advance,
Robin
 
Hi
try
SUMIF(A:A,"Milk",B:B)

not sure how you store your dates of the purchase. You only mentioned
two rows?
 
Thanks Frank,

worked a treat!

I already have dates stored, and many other columns, but just didn't mention
it as I was only interested in the Sumif formula.

Thanks for the help.

Robin
 
Oh, I realised something else!

Is it possible to do a someif command across multiple worksheets? e.g. to
count the Milks across 8 worksheets?

=sumif($sheet1$:$sheet10$a:a, "Milk",b:b)

The above formula is my attempt at making it read the values from sheets 1
to 10. I'm assuming it wouldn't work, but that there is some way of doing
it!

Thanks,
Robin
 
Hi
some solutions:
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula
=SUM((THREED('sheet1:sheet10'!A1:A1000)="Milk")*(THREED('sheet12:sheet1
0'!B
1:B1000)))

enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column Bin which column A contains our criteria
 
One more way, maybe....

Can you dedicate a cell on each worksheet in the same position.

Then you could have each of those cells do the =sumif() for their own sheet.

Then
=sum(sheet1:sheet10!b1)

(if B1 were the dedicated cell.)
 
Thanks all.

Peter, that seems the easiest way, so I'll give that a blast and see if it
works.

Cheers
Robin
 
Back
Top