Microsoft Office Forums Adding up cells if value equals

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-15-2014, 06:03 AM
Kh1978 Kh1978 is offline Adding up cells if value equals Windows 7 32bit Adding up cells if value equals Office 2010 32bit
Novice
Adding up cells if value equals
 
Join Date: May 2014
Posts: 4
Kh1978 is on a distinguished road
Default Adding up cells if value equals

Please help regarding a problem in Excel.



I need to add upp the total sum Kr for all columns of type 1.
What is the correct formula for this if the kr is to be summed up from C4:O39 and the Type row is between P:4:P39?
Many thanks !
K
areakrkrkrkrtype
100 kr 2 type
100 2 type
100 1 type
200 1 type
200 1 type
Attached Files
File Type: xlsx Excel ex.xlsx (8.9 KB, 4 views)

Last edited by Kh1978; 07-15-2014 at 10:42 AM. Reason: added an excelfile
Reply With Quote
  #2  
Old 07-15-2014, 06:32 AM
SarahBear SarahBear is offline Adding up cells if value equals Windows 7 32bit Adding up cells if value equals Office 2010 32bit
Novice
 
Join Date: Jun 2014
Posts: 13
SarahBear is on a distinguished road
Default

Can you attach a file with what your data looks like to begin with, and what you want it to look like in the end? I'm having too much trouble following your description to decide if I can help you with your question.
Reply With Quote
  #3  
Old 07-15-2014, 09:54 AM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Adding up cells if value equals Windows XP Adding up cells if value equals Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

I agree with Sarah. At any rate, it's unclear whether column P has numeric values or strings; perhaps you'll need
=sumif(p$4: p$39, 1, c$4:c$39)
+sumif(p$4: p$39, 1, d$4:d$39)
+sumif(p$4: p$39, 1, e$4:e$39)
+sumif(p$4: p$39, 1, f$4:f$39)
+sumif(p$4: p$39, 1, g$4:g$39)
+sumif(p$4: p$39, 1, h$4:h$39)
+sumif(p$4: p$39, 1, i$4:i$39)
+sumif(p$4: p$39, 1, j$4:j$39)
+sumif(p$4: p$39, 1, k$4:k$39)
+sumif(p$4: p$39, 1, l$4:l$39)
+sumif(p$4: p$39, 1, m$4:m$39)
+sumif(p$4: p$39, 1, n$4:n$39)
+sumif(p$4: p$39, 1, o$4: O$39)

I'm unclear how to selectively suppress emoticons, but the left side of each above is : and P without a space and the last item on the right is : and O with no space
Reply With Quote
  #4  
Old 07-15-2014, 10:44 AM
Kh1978 Kh1978 is offline Adding up cells if value equals Windows 7 32bit Adding up cells if value equals Office 2010 32bit
Novice
Adding up cells if value equals
 
Join Date: May 2014
Posts: 4
Kh1978 is on a distinguished road
Default

Thanks
I have added an example Excel.
Thus there are several sheets as well I need to add the sum of all with type 1 for all sheets in the same excelfile.

Hope it is clear?!
/Kris
Reply With Quote
  #5  
Old 07-15-2014, 11:52 AM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Adding up cells if value equals Windows XP Adding up cells if value equals Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

It is better to explain what you want to do more completely. Do you wish to sum the cells in
Code:
A2:D6
for type 1? Are A, B, C and D always the same as each other? For the data you included, is the desired result 1200? Does each sheet use columns A through E the same way?

Mod, I'm getting killed by emoticons. Is there a "quick disconnect" on one post or symbol?
Reply With Quote
  #6  
Old 07-15-2014, 12:06 PM
Kh1978 Kh1978 is offline Adding up cells if value equals Windows 7 32bit Adding up cells if value equals Office 2010 32bit
Novice
Adding up cells if value equals
 
Join Date: May 2014
Posts: 4
Kh1978 is on a distinguished road
Default 1200 +1200

I would like to sum all the kr for type 1 in the cells and sum up total for sheet Oct and Nov as in the example
Thus the total I am looking for is all kr for type 1 summed up for sheet oct and nov (1200 +1200)

Thanks
Kris
Reply With Quote
  #7  
Old 07-15-2014, 12:48 PM
gebobs gebobs is offline Adding up cells if value equals Windows 7 64bit Adding up cells if value equals Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

There are probably better ways of doing this, but not knowing why you have your sheets set up the way they are, I suggest the most direct and easiest solution. For each row in each sheet, you could have an equation like

=IF(E2=1,SUM(A2: D2),0)

and then you would need to sum this column in each sheet and sum each of those results.

Alternatively, you could have one equation for each sheet like:

=sumif(E2:E6,1,A2:A6)+sumif(E2:E6,1,B2:B6)+sumif(E 2:E6,1,C2:C6)+sumif(E2:E6,1,D2: D6)

and then sum those results for each month.

Or even combine the equations for each sheet into a single equation though as the number of calculated cells decrease, the equation complexity increases.

Or you could redesign your sheets. Why not have all the data on a single sheet with the additional field for Month?



PS...the emoticons are annoying. First I have noticed it.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell equals zero or another cell in IF statement dirtboy Excel 3 06-20-2014 01:44 PM
how to keep text format when adding new cells? hotmud Word Tables 1 09-29-2013 06:12 PM
Adding up cells if value equals How-TO format cells (FILL) by comparing cells zanat0s Excel 1 07-03-2012 04:27 AM
Adding up cells if value equals Using (adding) different cells geoff Excel 3 11-23-2011 01:20 AM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM


All times are GMT -7. The time now is 05:22 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft