To countifs or not to countifs

T

tammy01977

New Member
Joined
Feb 12, 2019
Messages
6
This is the information. We are wanting to create another sheet that will add up all the coat, blast, and cutout for a certain date. I only know a little bit about the formulas and all the websites say to use this formula countifs. I am clueless as how to use it, especially since I have 4 columns to get the information from.


[TABLE="width: 524"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]POD DATE[/TD]
[TD]COAT[/TD]
[TD]BLAST[/TD]
[TD]CUTOUT[/TD]
[/TR]
[TR]
[TD]2/15/2019[/TD]
[TD]0.00[/TD]
[TD]2321.00[/TD]
[TD]3373.00[/TD]
[/TR]
[TR]
[TD]2/22/2019[/TD]
[TD]5471.00[/TD]
[TD]2321.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]0.00[/TD]
[TD]2321.00[/TD]
[TD]3373.00[/TD]
[/TR]
[TR]
[TD]2/15/2019[/TD]
[TD]0.00[/TD]
[TD]2321.00[/TD]
[TD]3373.00[/TD]
[/TR]
[TR]
[TD]3/15/2019[/TD]
[TD]0.00[/TD]
[TD]2321.00[/TD]
[TD]3373.00[/TD]
[/TR]
[TR]
[TD]3/22/2019[/TD]
[TD]0.00[/TD]
[TD]2321.00[/TD]
[TD]3373.00[/TD]
[/TR]
[TR]
[TD]2/15/2019[/TD]
[TD]0.00[/TD]
[TD]2321.00[/TD]
[TD]3373.00[/TD]
[/TR]
[TR]
[TD]2/22/2019[/TD]
[TD]0.00[/TD]
[TD]2321.00[/TD]
[TD]3373.00[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 524"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]POD DATE[/TD]
[TD]COAT[/TD]
[TD]BLAST[/TD]
[TD]CUTOUT[/TD]
[/TR]
[TR]
[TD]2/15/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2/22/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/8/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/15/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/22/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/29/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4/5/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Hi,

By your description, if the Columns in the New Table is in the same Order as the first Table, you'll want to use SUMIF:


Book1
ABCD
1POD DATECOATBLASTCUTOUT
22/15/2019023213373
32/22/2019547123210
43/1/2019023213373
52/15/2019023213373
63/15/2019023213373
73/22/2019023213373
82/15/2019023213373
92/22/2019023213373
10
11
12
13POD DATECOATBLASTCUTOUT
142/15/20190696310119
152/22/2019547146423373
163/1/2019023213373
173/8/2019000
183/15/2019023213373
193/22/2019023213373
203/29/2019000
214/5/2019000
Sheet560
Cell Formulas
RangeFormula
B14=SUMIF($A$2:$A$9,$A14,B$2:B$9)


B14 formula copied down and across to D21.
 
Upvote 0
you can use PivotTable or PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Source[/td][td][/td][td][/td][td][/td][td][/td][td]PivotTable[/td][td][/td][td][/td][td][/td][td][/td][td]PowerQuery[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]POD DATE[/td][td=bgcolor:#5B9BD5]COAT[/td][td=bgcolor:#5B9BD5]BLAST[/td][td=bgcolor:#5B9BD5]CUTOUT[/td][td][/td][td=bgcolor:#DDEBF7]Row Labels[/td][td=bgcolor:#DDEBF7]Sum of COAT[/td][td=bgcolor:#DDEBF7]Sum of BLAST[/td][td=bgcolor:#DDEBF7]Sum of CUTOUT[/td][td][/td][td=bgcolor:#70AD47]POD DATE[/td][td=bgcolor:#70AD47]Coat[/td][td=bgcolor:#70AD47]Blast[/td][td=bgcolor:#70AD47]Cutout[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
15/02/2019​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
2321​
[/td][td=bgcolor:#DDEBF7]
3373​
[/td][td][/td][td]15/02/2019[/td][td]
0​
[/td][td]
6963​
[/td][td]
10119​
[/td][td][/td][td=bgcolor:#E2EFDA]
15/02/2019​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
6963​
[/td][td=bgcolor:#E2EFDA]
10119​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
22/02/2019​
[/td][td]
5471​
[/td][td]
2321​
[/td][td]
0​
[/td][td][/td][td]22/02/2019[/td][td]
5471​
[/td][td]
4642​
[/td][td]
3373​
[/td][td][/td][td]
22/02/2019​
[/td][td]
5471​
[/td][td]
4642​
[/td][td]
3373​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
01/03/2019​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
2321​
[/td][td=bgcolor:#DDEBF7]
3373​
[/td][td][/td][td]01/03/2019[/td][td]
0​
[/td][td]
2321​
[/td][td]
3373​
[/td][td][/td][td=bgcolor:#E2EFDA]
01/03/2019​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
2321​
[/td][td=bgcolor:#E2EFDA]
3373​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
15/02/2019​
[/td][td]
0​
[/td][td]
2321​
[/td][td]
3373​
[/td][td][/td][td]15/03/2019[/td][td]
0​
[/td][td]
2321​
[/td][td]
3373​
[/td][td][/td][td]
15/03/2019​
[/td][td]
0​
[/td][td]
2321​
[/td][td]
3373​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
15/03/2019​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
2321​
[/td][td=bgcolor:#DDEBF7]
3373​
[/td][td][/td][td]22/03/2019[/td][td]
0​
[/td][td]
2321​
[/td][td]
3373​
[/td][td][/td][td=bgcolor:#E2EFDA]
22/03/2019​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
2321​
[/td][td=bgcolor:#E2EFDA]
3373​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
22/03/2019​
[/td][td]
0​
[/td][td]
2321​
[/td][td]
3373​
[/td][td][/td][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]
5471​
[/td][td=bgcolor:#DDEBF7]
18568​
[/td][td=bgcolor:#DDEBF7]
23611​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
15/02/2019​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
2321​
[/td][td=bgcolor:#DDEBF7]
3373​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
22/02/2019​
[/td][td]
0​
[/td][td]
2321​
[/td][td]
3373​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

M-code for PowerQuery
Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"POD DATE"}, {{"Coat", each List.Sum([COAT]), type number}, {"Blast", each List.Sum([BLAST]), type number}, {"Cutout", each List.Sum([CUTOUT]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"POD DATE", type date}})
in
    #"Changed Type1"[/SIZE]
 
Upvote 0
jtakw said:
Hi,

By your description, if the Columns in the New Table is in the same Order as the first Table, you'll want to use SUMIF:

ABCD
POD DATECOATBLASTCUTOUT
POD DATECOATBLASTCUTOUT

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2/15/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2/22/2019[/TD]
[TD="align: right"]5471[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2/15/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3/15/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3/22/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2/15/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2/22/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2/15/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6963[/TD]
[TD="align: right"]10119[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2/22/2019[/TD]
[TD="align: right"]5471[/TD]
[TD="align: right"]4642[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3/8/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]3/15/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]3/22/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2321[/TD]
[TD="align: right"]3373[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]3/29/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet560​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B14[/TH]
[TD="align: left"]=SUMIF($A$2:$A$9,$A14,B$2:B$9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



B14 formula copied down and across to D21.
Click to expand...



Thank you both for your assistance. I was getting a headache trying to get that countifs to work, LOL
 
Upvote 0
tammy01977 said:
I can't seem to make this work, LOL...
Click to expand...

How do you mean? How is it Not working for you?
Maybe a little more explanation?

BTW, you need to change/adjust cell references/range and add sheet name as needed to the formula...
 
Upvote 0
jtakw said:
How do you mean? How is it Not working for you?
Maybe a little more explanation?

BTW, you need to change/adjust cell references/range and add sheet name as needed to the formula...
Click to expand...


_______________________________________________________________________________________________________


Oh, I am sorry. I was meaning this replying stuff.
 
Upvote 0
Ok, then I assume you got the SUMIF to work for you...

and You're welcome, welcome to the forum.
 
Upvote 0
Yes I got the sumif to work but the boss doesn't want it to add up all the dollar amounts, since they are different for each customer She wants it to add up the cells that has an amount in it. The only issue is that she has to have 0.00 in the fields. If that makes an sense. I have a hard time trying to explain things.

this is what she wants.

[TABLE="width: 291"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]POD DATE[/TD]
[TD]Lining[/TD]
[TD]Interior Blast[/TD]
[TD]Paint[/TD]
[/TR]
[TR]
[TD]3/22/2019[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3/29/2019[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4/5/2019[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]


i haven't had time to play with it much today.
 
Upvote 0
Ok, so we Will be using COUNTIFS if I understand correctly.

Change/adjust cell references/range, add sheet name, as needed:


Book1
ABCD
1POD DATECOATBLASTCUTOUT
22/15/2019023213373
32/22/2019547123210
43/1/2019023213373
52/15/2019023213373
63/15/2019023213373
73/22/2019023213373
82/15/2019023213373
92/22/2019023213373
10
11
12
13POD DATECOATBLASTCUTOUT
142/15/2019033
152/22/2019121
163/1/2019011
173/8/2019000
183/15/2019011
193/22/2019011
203/29/2019000
214/5/2019000
Sheet560
Cell Formulas
RangeFormula
B14=COUNTIFS($A$2:$A$9,$A14,B$2:B$9,">0")
 
Upvote 0
You must log in or register to reply here.

Similar threads

Cliffork
  • Solved
Print to PDF Macro, or possibly more
2 3
Replies
21
Views
314
mumps
mumps
C
  • Question Question
Subtotals and counta I think
Replies
1
Views
730
t0ny84
T
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
187
Omran Y
O
K
  • Question Question
Populate With Criteria
Replies
3
Views
79
Peter_SSs
Peter_SSs
Ramballah
  • Solved
Removing duplicates and adding amounts
2
Replies
18
Views
373
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top