sumif with three conditions - Please Help!

Posted by itgirl168 on July 29, 2001 6:05 PM

I have a spreadsheet that has two columns. I need to add all instances where the first column has the word "network" and second column equals 1. Currently I have a column for "network", a separate column for "network control", another column for "network problem", and yet another column to combine those totals. Is it possible to combine those instances in ONE? I've tried all day and just can't figure it out.
My current formula reads:
=SUM(IF($E$3:$E$401="Network Control Center",IF($F$3:$F$401=J75,1),0))

sample spreadsheet:
control 5
control 1
network 1
network 2
control 5
network control 1
network control 4
network-problem 1
network 8

Thanks!!!!!!!!!

Posted by Aladin Akyurek on July 30, 2001 12:42 AM

Lets say that you have all possible descriptions (i.e., control, network, network control, network problem) in one column, say A, and numbers in say column B.

In order to COUNT all occurrences containing the word "network" with a corresponding 1 in B (a case of multiconditional count), use:

=SUMPRODUCT(ISNUMBER(SEARCH("network",A2:A10))*(B2:B10=1))

In order to SUM (i.e., total) the values in B with a corresponding occurence in A that contains the word "network" (a case of multiconditional sum), use:

=SUMPRODUCT(ISNUMBER(SEARCH("network",A2:A10))*(B2:B10))

Given the following data in A2:B10

{"control",5;"control",1;"network",1;"network",2;"control",5;"network control",1;"network control",4;"network problem",1;"network",8}

the count (by the first formula) is: 3

the total is: 27

If you need more, post 10 rows of your data from all relevant columns along with what you want.

Aladin

===================

Posted by itgirl168 on July 30, 2001 11:13 AM

Thanks for your help Aladin! I really appreciate it!

:
Lets say that you have all possible descriptions (i.e., control, network, network control, network problem) in one column, say A, and numbers in say column B. : My current formula reads