Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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!!!!!!!!!


Check out our Excel Resources

Re: sumif with three conditions - Please Help!

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

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


Re: sumif with three conditions - Please Help!

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.