Creating A Total Based On Multiple Conditions

  • Thread starter Thread starter MOOK
  • Start date Start date
M

MOOK

I'm working on a spreadsheet and my head has nearly exploded fro
frustration.
The problem is, i need the spreadsheet to count cells in one column bu
only if they hold certain data from another cell.

I found one sample formula which is:

=sum(if((B5:B25="NORTHWIND")*(C5:C25="WESTERN"),F5:F25))

but it doesn't seem to work, i'm quite new to excel so maybe i jus
don't understand it, so explaining what it means would be good.

Erm...... does that make sense????

Can anyone help before i go totally insane? :confused
 
Hi

You may need to enter that formula with Ctrl Shift and Enter - as it is an
array formula.
An alternative is to use SUMPRODUCT:
=SUMPRODUCT((B5:B25="NORTHWIND")*(C5:C25="WESTERN")*(F5:F25))

Hope this helps.
 
Try using =COUNTIF(A1:A30,7)

This reads as look in the range of cells A1 to A30 and count the cell
that contain the value 7
 
UNFORTUNATLY I'VE ALREADY TRIED THAT FORMULA AND IT DOESN'T WORK.
UNFORTUNATLEY FOR ME MY QUERY INVOLVES MULTIPLE ARGUMENTS AND THA
FORMULA IS ONLY GOOD FOR SINGLE ARGUMENTS.

I'VE TRIED TO CLARIFY EXACTLY WHAT I;M TRYING TO DO BELOW.

IF COLUMN A1 HAS THE NUMBER '29' IN IT THEN I WANT THE SPREADSHEET T
COUNT IT BUT ONLY IF THERE IS A '1' IN COLUMN B1
:confused
 
I had a similar problem trying to count the number of times a code letter/number appeared in a column based on criteria in other columns until I found that if I treated the numbers as such, instead of as text as they were formated to 'look' like on the spreadhseet, the function would count them correctly. Try something like this

=sum((ColumnName1="Text You're Looking For")*(ColumnName2=Numeric Your Looking For)

Enter this function as an array (control+shift+enter)

----- MOOK > wrote: ----

I'm working on a spreadsheet and my head has nearly exploded fro
frustration
The problem is, i need the spreadsheet to count cells in one column bu
only if they hold certain data from another cell.

I found one sample formula which is

=sum(if((B5:B25="NORTHWIND")*(C5:C25="WESTERN"),F5:F25)

but it doesn't seem to work, i'm quite new to excel so maybe i jus
don't understand it, so explaining what it means would be good

Erm...... does that make sense????

Can anyone help before i go totally insane? :confused
 
Back
Top